set analysis, null values, if statement

[Anonymous]

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.



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

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.

So, what am I doing wrong?

Thanks.

[eduardo_dimperio]

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

[Anonymous]

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.