Qlik Community

Qlik Sense App Development

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

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
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.

2 Replies
Highlighted
Specialist II
Specialist II

Ciao Samuel,

I think you could first create a table with Month,Year (Inline) and join a table with  Year,Month,SalesYear to put this zero where you dont have Sales, after that use set analysis

Highlighted
Contributor
Contributor

Hello Eduardo,

if I understood your answer, I think it wouldn't work as there are no rows having the product 'OP' in the fact table.

My main question is: what's wrong with the set analysis I am using? I mean, is it correct to use the if statement like I did?

Thanks.