3 Replies Latest reply: Apr 21, 2016 12:22 PM by Satish Kurra RSS

    KPI calculation error

    James Nolfo

      I am somewhat new to Qlik Sense and not really a code guy, but typically I can figure things out by looking at forums such as this.

       

      What I am trying to accomplish is a KPI that shows sales for the month. 

       

      When I do this formula (for the year), everything works out fine and I get my yearly total.

      Sum({<CALENDAR_YEAR={$(=vCurrentYear)}>} SumOfGROSS_SALE_AMT)

       

      However, when I do this, I get nothing.

      Sum({<CALENDAR_YEAR={$(=vCurrentYear)},{NUM(MONTH(TRANS_DATE))={$(=CurrentMonthNum)}>} SumOfGROSS_SALE_AMT)

       

      Note, I have in my data load editor the following

       

      LET vCurrentMonthNum = NUM(Month(Makedate(2016,3,31)));

      LET vCurrentMonth = Month(Makedate(2016,3,31));

      LET vCurrentYear = NUM(year(Makedate(2016,3,31)));

       

      Can anyone help me see what I may be doing wrong?

        • Re: KPI calculation error
          Sunny Talwar

          I would suggest you to create a Month field in the script and then may be try this:

           

          Sum({<CALENDAR_YEAR={$(=vCurrentYear)}, MONTH_FIELD={$(=CurrentMonthNum)}>} SumOfGROSS_SALE_AMT)

           

          LOAD MONTH(TRANS_DATE) as MONTH_FIELD

          • Re: KPI calculation error
            Gysbert Wassenaar
            LET vCurrentMonthNum = NUM(Month(Makedate(2016,3,31)));

            That's a rather complex way to basically do this: LET vCurrentMonthNum = 3;

             

            To answer your real question:

            You can only use real field names on the left side of the = in a set analysis expression. What you should do is first create a Month field in the data load editor:

             

            LOAD

                 ...lots of fields,

                 Num(Month(TRANDATE)) as CALENDAR_MONTHNUM

            FROM ...

             

            Then your expression can be changed to

            Sum({<CALENDAR_YEAR={$(=vCurrentYear)},CALENDAR_MONTHNUM={$(=vCurrentMonthNum)}>} SumOfGROSS_SALE_AMT)

            • Re: KPI calculation error
              Satish Kurra

              Try this

               

              Sum({<CALENDAR_YEAR={$(=vCurrentYear)},NUM(MONTH(TRANS_DATE))={$(=CurrentMonthNum)}>} SumOfGROSS_SALE_AMT)