set analysis according to user selection

[ashishtams]

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

[swuehl]

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?

[ashishtams]

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.

[swuehl]

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)

[punitpopli]

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

[rubenmarin]

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)