12 Replies Latest reply: Dec 27, 2017 7:34 PM by Denis Lomakin

# Cumulative Number on KPI

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.

Thanks

• ###### Re: Cumulative Number on KPI

That's more of YTD expression.

Please refer to this thread where I tried to explain step by step how to handle such things:

YTD, MTD issue

• ###### Re: Cumulative Number on KPI

Thanks Omar,

I probably did not explain my self correct.

Let me try in different way.

I have a survey date and then I need to create KPI that summarizing the score and dividing it by maximum score for each answer. This is what the four variables do.

So I build the KPI and created the Month selection.

Now the requirements are that when user select one month, November for example the KPI calculation should look back to prior data up to November and do the calculation.

For example:

Month     Score

Sep         20

Oct          30

Nov         10

Dec         20

So the total score is 80 and when user select November it should show 60.

I cannot do it with today date as it should be based on selection user made.

So even when I do rolling 3 months or rolling 6 months it should be based month selected so I cannot hard code the Max Date.

Hope these make sense.

Thanks

• ###### Re: Cumulative Number on KPI

Do u have a date field?

• ###### Re: Cumulative Number on KPI

if u do: have date, Month, Year.. whatever field u have:

try smthing like this:

sum({<date=,Year=,Month=,Quarter=, date={">=\$(=YearStart(Max(date)))<=\$(=Max(date))"}>}YourMeasure)

• ###### Re: Cumulative Number on KPI

Hi Omar,

Thanks for that.

It works with one measure but my problem is that my measures are like this:

If(sum(msrConvenienceIndexQuestionPoints1) > 0, sum(msrConvenienceIndexIndexScore1) / sum(msrConvenienceIndexQuestionPoints1), 0).

And I have four like that when at the end the sum of four is the main measure to score customer satisfaction.

In order not to add the measures in each table I created variables in the script but I am not sure how to use variable in the formula you provided.

Could you help me with that?

Thanks

• ###### Re: Cumulative Number on KPI

what if u alter it as follow?

if(sum({<date=,Year=,Month=,Quarter=, date={">=\$(=YearStart(Max(date)))<=\$(=Max(date))"}>}msrConvenienceIndexQuestionPoints1)>0,

sum({<date=,Year=,Month=,Quarter=, date={">=\$(=YearStart(Max(date)))<=\$(=Max(date))"}>}msrConvenienceIndexIndexScore1) / sum({<date=,Year=,Month=,Quarter=, date={">=\$(=YearStart(Max(date)))<=\$(=Max(date))"}>}msrConvenienceIndexQuestionPoints1),0)

• ###### Re: Cumulative Number on KPI

Hi Omar,

Thanks for that.

That would work but my only concerns is that I would have to it as a sum of four calculation like this and it will be too long.

That why I was wondering if I can add variable into set analysis.

But if it is not possible I can use that expression.

I will test it and see if it works.

Thanks

• ###### Re: Cumulative Number on KPI

well u can create a variable; let's call it : vCumul= date=,Year=,Month=,Quarter=, date={">=\$(=YearStart(Max(date)))<=\$(=Max(date))"}

and use it:

if(sum({<\$(vCumul)>}msrConvenienceIndexQuestionPoints1)>0,

sum({<\$(vCumul)>}msrConvenienceIndexIndexScore1) / sum({<\$(vCumul)>}msrConvenienceIndexQuestionPoints1),0)

• ###### Re: Cumulative Number on KPI

Yes that is also an option.

Thank you very much for your help.

I think this will work for me.

Thanks

• ###### Re: Cumulative Number on KPI

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:

• ###### Re: Cumulative Number on KPI

Maybe use a variable as follow: