set analysis, null values, if statement

Anonymous — Thu, 17 May 2018 10:51:11 GMT

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.

Re: set analysis, null values, if statement

eduardo_dimperio — Thu, 17 May 2018 17:33:31 GMT

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

Re: set analysis, null values, if statement

Anonymous — Fri, 18 May 2018 08:05:44 GMT

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.

topic Re: set analysis, null values, if statement in App Development

set analysis, null values, if statement

Re: set analysis, null values, if statement

Re: set analysis, null values, if statement