Solved

Cumulate function on Metrics and Filters.

  • 10 January 2023
  • 3 replies
  • 46 views

Userlevel 1
Badge

Hi Pigment community,

 

My case description:

I have 3 Transaction List [tl], which have accounting transactions, I added to those list identical dimension.

Then I have created a “Staging Metric” that’s adding up for 3 [tl] the column “USD Net value” (number property), on the following dimensions [by: Company, ‘Company and Account’, Month]

Note: ‘Company and Account’ dimension is the lowest level and from where stem the Reporting dimensions (Level 1, Level 2, Level 3).

The metric aggregates well, and adds up the transaction values at each reporting level and at each time level (month, year, quarter).

Then I want to create a metric that does the cumulative for “USD Net value”, so I can get the values for the “Balance Sheet” cumulating on a monthly basis. I use the following syntax:

  1. Cumulate('Staging Metric'[Filter:'Company and Account’.Report = Report."Balance Sheet"],Month)
  2. Cumulate('Staging Metric',Month)

The formula works well when showing Month Dimension in the columns

 

The problem: When showing the Years only the numbers doesn’t add up.

How should I approach the problem to get proper cumulative figures on a ‘Year’ dimension too?

 

Thanks in advance for your help here ;D

icon

Best answer by Matt D 11 January 2023, 10:17

View original

3 replies

Userlevel 2
Badge +1

Hi Guillermo,

Thanks for the detailed explanation of the issue you are facing.

The setting that should help you out is found in the ‘View Aggregators’ options in the toolbar at the top of the metric view.

Within here, you can set the aggregation method of any dimensions, including hidden ones (in your example, ‘Month’ is the hidden dimension). The default aggregation method is ‘Sum’, but for a metric showing balances, we want this to be set as ‘Last’.

See the screenshot below to see the settings panel I’m referring to.

 

Please let me know if you need anything else :)

Cheers,

Userlevel 1
Badge

Hi Matt D,

 

Certainly aggregates well now, thanks a lot :D

Just for curiosity: Now wen drilling to Transaction still get the disclaimer “Filtering is not accurate in this formula”, when getting to transaction level I get the transactions of each period properly, is there a way to get the cumulative transactions?

 

Best,

Guillermo

Userlevel 2
Badge +1

Hi Guillermo,

I’m afraid I do not think it is not possible to view the cumulated position of a numeric property in a transactional list via ‘drill to transaction’. If you were to load the cumulated values in then it would show you what you need to, but not in this case!

Cheers,

Reply