topic Re: Set analysis help for total count as per date in QlikView

Set analysis help for total count as per date

ashishtams — Fri, 06 Nov 2015 10:41:47 GMT

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

Re: Set analysis help for total count as per date

swuehl — Fri, 06 Nov 2015 13:10:03 GMT

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)

Re: Set analysis help for total count as per date

Anonymous — Fri, 06 Nov 2015 13:13:45 GMT

Bypass your master calender date:

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

Re: Set analysis help for total count as per date

ashishtams — Fri, 06 Nov 2015 13:17:22 GMT

It worked as expected

Re: Set analysis help for total count as per date

rubenmarin — Fri, 06 Nov 2015 13:18:42 GMT

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)