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

    Cumulative Number on KPI

    Denis Lomakin

      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

        • Re: Cumulative Number on KPI
          omar bensalem

          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
              Denis Lomakin

              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

                • Re: Cumulative Number on KPI
                  omar bensalem

                  Do u have a date field?

                  • Re: Cumulative Number on KPI
                    omar bensalem

                    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
                        Denis Lomakin

                        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
                            omar bensalem

                            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
                                Denis Lomakin

                                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
                        omar bensalem

                        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)