Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.

Any ideas please?

Thanks

1 Solution

Accepted Solutions
OmarBenSalem

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)

View solution in original post

12 Replies
OmarBenSalem

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

Anonymous
Not applicable
Author

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.

Please help

Thanks

OmarBenSalem

Do u have a date field?

OmarBenSalem

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)


Anonymous
Not applicable
Author

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

OmarBenSalem

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)

Anonymous
Not applicable
Author

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

OmarBenSalem

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)

Anonymous
Not applicable
Author

Yes that is also an option.

Thank you very much for your help.

I think this will work for me.

Thanks