0 Replies Latest reply: May 17, 2018 6:48 AM by Samuel Nicoli RSS

    set analysis, null values, if statement

    Samuel Nicoli

      Hello there,

       

      I am trying to show in a straight table the sales amount for each month of the year (until the current one). Some months have no sales recorded, so I need a 0 output for them.
      The sales amount is filtered by product (Product = 'OP').

       

      I am using the following set analysis but the result is not correct:

       

       

      =SUM({<SalesYear = {$(vCurrentYear)}, YEAR = {$(#vYear)}, MONTH = {$(#vMonth)},

                     Product = {"OP"}>

                    +

                  <SalesYear  = {$(vCurrentYear)}, YEAR = {$(#vYear)}, MONTH = {$(#vMonth)},

                     Product -= {"OP"}>

         }

        if(Product ='OP', Amount, 0) )


      Note: YEAR and MONTH are used to show the data to a specific month of the year. If YEAR = 2018 and Month = '04' the report is updated at April, 30th.


      I am using the if statement to choose whether or not to sum the 'Amount' field (is it possible/correct to do this?). But for some reason the amount returned for January is wrong (doubled, precisely).

      I checked the Data Model and there are no no problems with it.

      Here's the table with the output.

      QS_wrong.PNG

      Here are the expected amounts (this shows that the Data Model is correctly designed):
      QS_right.png

       

       

      The thing is, I tried to sum 1 if Product = 'OP' (0 otherwise) and I get the right result:

       

      =SUM({<SalesYear = {$(vCurrentYear)}, YEAR = {$(#vYear)}, MONTH = {$(#vMonth)},

                    Product = {"OP"}>

                    +

                  <SalesYear  = {$(vCurrentYear)}, YEAR = {$(#vYear)}, MONTH = {$(#vMonth)},

                    Product -= {"OP"}>

        }

        if(Product ='OP', 1, 0) )

       

      I have 1 sales recorded in January and 4 sales recorded in March.

       

      qliksense_right2.PNG

       

      So, what am I doing wrong?

       


      Thanks.