Skip to main content

Hello Pigment!

We trying to cumulate values on 1 metric based on ranking, but this metric has 2 dimensions on its structure (Customer, Region), so we have the Ranking Metric and To Cumulate Metric, ex:

 

Region
(Dimension)		 Customer
(Dimension)		 To Cumulate
(Metric)		 Ranking
(Metric)		 Cumulate on Rank
(Metric)
1 C1 50 5 148
2 C2 20 2 30
3 C3 30 3 60
1 C4 80 6 228
2 C5 110 9

513
3 C6 90 8 403
1 C7 85 7 313
2 C8 38 4 98
3 C9 10 1 10



The current issue its if we try to do that using CUMULATE, we can only set the accumulation through 1 dimension (either Customer or Region) and using 2 cumulates at the same time seems not to be the solution so far

Any recommendations? 

Regards,

Agarneri

Hello, 
Here is my solution. You will need to create a new dimension called “Ranking Dimension”, which will be used to turn the numeric ranking into a dimension that can then be used for calculation. 
Here are the metrics that I created:

  • MET_To_Cumulate
    • Dimensions: Region, Customer
    • Manual values based on your screenshot
  • MET_Ranking
    • Dimensions: Region, Customer
    • Manual values based on your screenshot
  • MET_Ranking_Dimension
    • Dimension: Region, Customer, Ranking Dimension
    • Formula: ITEM(TEXT(MET_Ranking), 'Ranking Dimension'.Name)
    • Note: This is where I use the new dimension I created. The list just contains items from 1-9. 
  • MET_To_Cumulate_with_Ranking
    • Dimension: Region, Customer, Ranking Dimension
    • Formula: MET_To_Cumulate_BY:-> MET_Ranking_Dimension]
  • MET_Cumulated_Values
    • Dimensions: Ranking Dimension
    • Formula: CUMULATE(MET_To_Cumulate_with_RankingtREMOVE:Customer, Region], 'Ranking Dimension')
    • Values are cumulated based on the Ranking Dimension
  • MET_Cumulate_on_Rank
    • Dimension: Region, Customer
    • Formula: MET_Cumulated_ValuesaBY:MET_Ranking_Dimension]
    • End result, which will match your screenshot

Hope this helps :) 

Hello @Lehtohen !

Thank you very much. In fact, this was one of the alternatives we found, and as a result, we created this post. The customer data changes quite frequently, and we would need to do large imports on a regular basis. Additionally, our application is already quite large (+900 metrics, not counting that we use the metrics as often as possible by renaming, 'show as value of,' etc.). Customers number around 800 records, and regions have about 20 records, which would also be a fairly large dimension

That is indeed the best response; however, I will wait a bit longer in case someone has an alternative. Again, I appreciate your detailed reply

Regards,
Agarneri


 

Reply


Ask your question here!

Terms & ConditionsCookie settings