Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

denisenprecis
New Contributor

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
Esteemed Contributor

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)

12 Replies
OmarBenSalem
Esteemed Contributor

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

denisenprecis
New Contributor

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.

Please help

Thanks

OmarBenSalem
Esteemed Contributor

Re: Cumulative Number on KPI

Do u have a date field?

OmarBenSalem
Esteemed Contributor

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)


denisenprecis
New Contributor

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

OmarBenSalem
Esteemed Contributor

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)

denisenprecis
New Contributor

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

OmarBenSalem
Esteemed Contributor

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)

denisenprecis
New Contributor

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

Community Browser