Set analysis help for total count as per date

[ashishtams]

Hi All,

I have used below expression to calculate the total sale for the latest date. (by default)

Current_Sale          =Sum({$<BUSS_DATE={"$(=DATE(MAX(BUSS_DATE)))"}>}Sales)

Now i also wanted to show the previous sale, so i calculated the sale for previous date using below expression.

Previous_Sale         =Sum({$<BUSS_DATE={"$(=DATE(MAX(BUSS_DATE,2)))"}>}Sales)

It is giving me the correct results as of now. But the moment i select a date (any date), the previous_sale is not shown. it becomes null.

Only the current_Sale is shown for the selected date.

I want to show the previous sale even though a date is selected as a filter. So whichever date is selected that becomes my current date and then i want to calculate the previous sale accordingly.

is there anything i can use in the set expression to achieve this?

Thanks

Ashish

[swuehl · Accepted solution]

You need to change two things:

a) ignore selections in your max() function.

b) ignore selections in any calendar field in your set expression to avoid incompatible sets

Sum({$<BUSS_DATE={"$(=DATE(MAX({1} BUSS_DATE,2)))"}, DATEFIELDWITHUSERSELECTION= >}Sales)

[Anonymous]

Bypass your master calender date:

=Sum({$<MasterCalenderDate=,BUSS_DATE={"$(=DATE(MAX(BUSS_DATE,2)))"}>}Sales)

[ashishtams]

It worked as expected

[rubenmarin]

Hi, when you select a date, there is only one value, so there is no 2nd max and the previous date returns Null().

You can try to substract a day from the max date:

Previous_Sale         =Sum({$<BUSS_DATE={"$(=DATE(AddDays(MAX(BUSS_DATE), -1)))"}>}Sales)

or:

Previous_Sale         =Sum({$<BUSS_DATE={"$(=DATE(MAX(BUSS_DATE)-1))"}>}Sales)