Skip to main content

I have a dimension that is a number (30 Day Cohort).  I think I will need an identity matrix for some calculations later in my model.  Is it possible to create a metric that has the 30 Day Cohort x 30 Day Cohort?  Here is a simple example of what it would theoretically look like.

I have tried playing around with the Value function to convert to numbers and do some sort of comparison to the dimension but it doesn’t return what I would like:

IF(VALUE('30 Day Cohort'.Name)<VALUE('30 Day Cohort'.Name),0,1),BY SUM: '30 Day Cohort']

Any ideas or things to try would be much appreciated.  At the end of the day, the 1s in the matrix will eventually be values calculated from other metrics but I think I need this matrix to do those calculations.  But then again I may not given Pigment’s various functions and how it handles dimensions.  So any and all ideas are welcomed :)  Thank you in advance!

May have figured out a workaround but would still be interested in anyone else’s thoughts on this for a more sophisticated solution.

I simply created another dimension that mirrors my ‘30 Day Cohort’ dimension and then I can include it in the metric and compare the values in the IF statement to populate the 0s and 1s.  Just curious, is the “duplicate dimension” approach something anyone else has tried or if it leads to issues down the road?

Hi,

Not sure to quite fully understand your need, but what I’ve came across is usually a cohort applied on Months or Days, so the mapping is done between the ‘30 day cohort’ dimension and the Day dimension.

Would this help:

Metric type = dimension: Day

Structured by Day and ‘30 day cohort’

Formula: timedim(Day.'Start Date'Sadd:'30 day cohort']+'30 day cohort'.Integer,Day)

where '30 day cohort'.Integer  is the equivalent of your name value. (value of the cohort from 1 to 30).

 

Please let us know.

In any case, you can’t have both ‘30 day cohort’ as a dimension in rows and cols.

What Kevin suggested, mirroring the dimension could be a workaround. This would probably be an interesting one to share to the Community. 😊

Thanks for the reply and confirming you cannot have the same dimension in rows and cols.  Your other code is useful as well for something down the road but doesn’t quite answer this exact use case.

I ended up doing the duplicate dimension approach and it was fairly simple once I had that.  This table is used in other metrics for specific calculations so the 1s and blanks work fairly well.  The exclude modifier simply removes a value I created as “Null” for all the blank values in my source data.

 

IF(VALUE('30 Day Cohort Duplicate'.Name)<=VALUE('30 Day Cohort'.Name),1,BLANK)


 EXCLUDE: '30 Day Cohort'='30 Day Cohort'."Null" OR '30 Day Cohort Duplicate'='30 Day Cohort Duplicate'."Null"]

 

 

Reply


Ask your question here!

Terms & ConditionsCookie settings