LASTNONBLANK to return dimension from target metric?

  • 15 February 2023
  • 2 replies

Userlevel 3
Badge +2

Hi Pigment team!


I’m trying to set up some cohort modeling for launches. I have an input metric, of dimensions [Partner] and [Cohort Month], where Cohort Month is a 0-indexed integer list. The inputs are the number of live clients in that month (it’s chunky, usually a piecewise rollout for the first 3-6 months).


Let’s say partner A’s launch is coming up. Launch month is Cohort Month 0:


                            0           :           1           :             2          :          etc.            

Partner A           100                   500                    1000                  etc.


I want to know when to switch from this manual input metric to my growth curve assumption for the forecast, so I have to map these cohort months to calendar months.


I think the best way to do this is to have a mapping metric which returns the lastnonblank [Cohort Month] as an integer from the input metric outlined above. I can then use TIMEDIM to add that number of cohort months out from the launch month and finally determine the last calendar month for which we have manual inputs.


For example, if Partner A launches on March 1, 2023 and we have manual inputs for 3 months (0,1,2), the mapping metric would return “the lastnonblank Cohort month value is 2. I need to add 2 months to the launch month of March 2023, therefore the lastnonblank Calendar month value is May 2023.”


I can get the mapping metric to return the actual value from the input metric (in this example, 1000) but I can’t get the modifiers right to use the Cohort Month.


'25. INPUT_Asm_Full_Launch_Ramp_Active_Employers' [remove lastnonblank: 'Cohort Month']


Please let me know if you have any advice! Thanks.


Best answer by francois 15 February 2023, 20:28

View original

2 replies

Userlevel 6
Badge +7


Similarly to what I’ve shared in this article, you could replace your data by the Cohort Month itself.

IF(ISDEFINED('25. INPUT_Asm_Full_Launch_Ramp_Active_Employers'),'Cohort Month') [remove lastnonblank: 'Cohort Month']

That way you’re turning your numbers in Cohort Month and then removing the dimension.


On a side note, to turn your Cohort Month into Month, you could try using the SHIFT function.

Curious to see if somebody else has another solution!

Userlevel 3
Badge +2

Perfect! Thank you. My “Launch Month” metric is of type [Date] so I used TIMEDIM to get me to the [Month] dimension, and then selected the integer value within the Cohort Month dimension so that SHIFT would work!


This will prevent me having to do a manual cutover date for the forecast version/scenario 🙃


shift(timedim('15. INPUT_Partner_Full_Launch_Date',Month), IF(ISDEFINED('25. INPUT_Asm_Full_Launch_Ramp_Active_Employers'),'Cohort Month'.'Cohort Month') [remove lastnonblank: 'Cohort Month'])