Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

samuel_nicoli
New Contributor

set analysis, null values, if statement

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.

Community Browser