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

# 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.

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.