Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community,
I have a measures created as variables
let vConvenienceIndex1 = 'If(sum(msrConvenienceIndexQuestionPoints1) > 0, sum(msrConvenienceIndexIndexScore1) / sum(msrConvenienceIndexQuestionPoints1), 0) ';
let vTreatmentIndex1 = 'If(sum(msrTreatmentIndexQuestionPoints1) > 0, sum(msrTreatmentIndexIndexScore1) / sum(msrTreatmentIndexQuestionPoints1), 0)';
let vValueIndex1 = 'If(sum(msrValueIndexQuestionPoints1) > 0, sum(msrValueIndexIndexScore1) / sum(msrValueIndexQuestionPoints1), 0)';
let vQualityIndex1 = 'If(sum(msrQualityIndexQuestionPoints1) > 0, sum(msrQualityIndexIndexScore1) / sum(msrQualityIndexQuestionPoints1), 0)';
and then one measure is the sum of these variable
let vCSE = '$(vConvenienceIndex1) + $(vTreatmentIndex1) + $(vValueIndex1) + $(vQualityIndex1)' ;
What I am trying to do is create a KPI for the last measure vCSE but based on cumulative calculation for each month.
For example when I select Jan 2017 it gives result for Jan only, but if I select Feb 2017 (without selecting Jan 2017) it gives me cumulative result for Jan and Feb.
I tried to do it with rangesum but it did not work.
Any ideas please?
Thanks
HI Omar,
One last question related to the same issue.
If I want to add the same calculation to the chart with MonthYear dimension, but I need it to display only last 3 months based on Month Year selection. For example if i select Sep 2017 the chart will show July, August September,
I tried something like below but it still shows all the Months Would you be able to help me with this one too?
Thanks:
if(sum({<SurveyNewDate={">$(monthend(AddMonths(Max(SurveyNewDate),-3)))"}>}(msrConvenienceIndexQuestionPoints1))>0,
sum({<SurveyNewDate={">$(monthend(AddMonths(Max(SurveyNewDate),-3)))"}>}(msrConvenienceIndexIndexScore1))/
sum({<SurveyNewDate={">$(monthend(AddMonths(Max(SurveyNewDate),-3)))"}>}(msrConvenienceIndexQuestionPoints1)),0)
Maybe use a variable as follow:
vCumul3Months = date=,Year=,Month=,Quarter=, date={">=$(=addMonths(max(date),-3))<=$(=Max(date))"}
Great,
Thank you