Solved

Aligning dimensionality across metrics

  • 27 July 2023
  • 2 replies
  • 314 views
Kyla
Rank
Userlevel 2
Badge +3
  • Kyla Budding Pigmenteer
  • Budding Pigmenteer
  • 6 replies

Hi Community,

I’ve created a Commissions Forecast metric to calculate the difference between YTD Commissions and Total Target Commissions. The value is then spread over the remaining months left in the current year. 

I’d like to maintain the values from this metric and have it to flow through to my Total Compensation metric which has more dimensions defined in the metric structure. Any tips on how to accomplish this? 


Commissions Forecast Metric Dimensions: Department, Month

Total Compensation Metric Dimensions: Department, Month, ID, Employment Type, Employment Status, Location, State. 

icon

Best answer by Kyla 1 August 2023, 16:53

View original

2 replies

Benoit
Rank
Userlevel 6
Badge +12

Hi

To align your formula, you can use the formula playground from your total compensation metric and play with the formula and modifiers.

To allocate your source metric Commissions Forecast to your target metric total Compensation, you will need to add the missing dimensions.

In this case: ID, Employment Type, Employment Status, Location, State. 

Pigment automatically does it as you can see on the formula playground above. It takes the results and allocate by constant a result on all missing dimensions, so your source metric will be copied over the total cardinality of your missing dimensions.

So when you just add the source metric to a target that has more dimensions, in reality Pigment does an implicit allocation:

'commissions Forecast' [add constant: ID,'Employment type','Employment status',Location,State]

Which I reflected in the table below.

 

 

Another logic would be to split the results accross your missing dimension so the aggregated total is exactly the same as your source metric, using the modifier ADD split:

'commissions Forecast' [add split: ID,'Employment type','Employment status',Location,State]

Which I also reflected on the table.

 

On the last line of the table you can see that allocating using add split reflect the results of your source metric.

 

To know more about add and modifiers:

Hope it helps!

Best,

Benoit

 

 

Benoit - Pigment wizard
Kyla
Rank
Userlevel 2
Badge +3

Thanks for your response, Benoit! That makes sense, however I received an error message indicating a long run time: “Formula timed out (> 3 minutes)”. After working with the support team, we decided to move forward with the following formula below: 

'Commissions Forecast'
[BY SPLIT: ID.'Curr Dept']
[BY: ID, ID.'Curr Dept', ID.'Curr Emp Type', ID.'Curr Emp Status', ID.'Curr State', ID.'Curr Location']
 

Instead of using the Department dimension, we defined our modifier using the ID dimension given the other dimensions (i.e. Department, Employment Type, Employment Status, State, Location) were properties of the ID dimension. This resolved the timeout message and returned the target data.  

Reply


Ask your question here!

Terms & ConditionsCookie settings