topic Re: set analysis according to user selection in QlikView

set analysis according to user selection

ashishtams — Thu, 12 Nov 2015 12:06:20 GMT

Hi All

I have a scenario where i calculate Sales trend using the current date and the previous date.

So i calculate the current and previous Sales using below expressions

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

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

However lets say i am showing trend for a particular product and it has the value of sales for current date and immediate previous dates.

Like for eg. product A has sales value for 5thOct and 4thOct then i am able to see the sales trend correctly.

However if the Product B has sales for 5thoct and for 3rdOct then i am not able to see the trend correctly. As there is date of 4th Oct but no sale value. its not showing any data for previous_Sales

I want to understand that even if the previous date is not the immediate date then how should i calculate the Previous_Sales.

Thanks

Ashish

Re: set analysis according to user selection

swuehl — Thu, 12 Nov 2015 12:25:45 GMT

You can't use set analysis {1} in the DATE() function, so I assume that's a typo and should be within MAX().

What if you just remove the {1} completely?

Re: set analysis according to user selection

ashishtams — Thu, 12 Nov 2015 12:50:19 GMT

Yes its a typo.

its within the MAX..

if i remove the one completely then there is a problem. like if any random date is selected from the available dates then i cannot calculate the previous date after the selection. so i need {1} there to show trend regardless of any date selection.

Re: set analysis according to user selection

swuehl — Thu, 12 Nov 2015 13:16:24 GMT

Ok, understood. But I believe using {1} won't return the correct preceding date, if you are not selecting the max BUSS_DATE available in your record set. Assuming you are only making user selections on BUSS_DATE:

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

Re: set analysis according to user selection

punitpopli — Thu, 12 Nov 2015 13:47:27 GMT

Add Sum(0) with the existing formula this will populate the date with no values in the graph but if you do any selection this will not work as expected.

Thanks,

Punit

Re: set analysis according to user selection

rubenmarin — Thu, 12 Nov 2015 14:05:09 GMT

Add to retrieve the last date with sales you can try with:

=Sum({$<BUSS_DATE={"$(=DATE(MAX({<BUSS_DATE = {'<$(=Date(MAX(BUSS_DATE)))'}, Sales={'*'}>} BUSS_DATE)))"}>} Sales)