Aging dates based on selected date in list box selection

[#1]

Hi Please find the data sheet attached. i want sales of

products=A  & Status = open & sales of 30 days previous of date selected on list box.

previous 31 to 60 days sales

& 61 to 90 days

& > 90

[marcus_sommer]

You could use set analysis for this, with an expression for each period to query, maybe in this way:

sum({< Status = {'open'}, Products = {'A'},

     [Order Date] = {">=$(=date(max([Order date]) - 30, 'MM/DD/YYYY'))"}>} Sales)

sum({< Status = {'open'}, Products = {'A'},

     [Order Date] = {">=$(=date(max([Order date]) - 60, 'MM/DD/YYYY'))<$(=date(max([Order date]) - 30, 'MM/DD/YYYY'))"}>} Sales)

Maybe it needs some adjustements on the date-formats within the set analysis which must be excactly the same like tis from field and with the >= operators and days which would be between them. As alternatively to max() you could also use getfieldselections().

- Marcus

[Kushal_Chawda · Accepted solution]

Please see the attached

[#4]

thanku bro  ur perfect

[Kushal_Chawda]

Glad that I was able to help

[maxgro]

you can use a straight table chart with

dimension  

City

expression (this is the expression for prev 30 days)  

sum({$ <Products={A},Status={Open},[Order Date]={"<=$(=date(max([Order Date])))>=$(=date(max([Order Date])-30))"}>} Sales)

add the other expression with the same logic, only change the bold part

sum({$ <Products={A},Status={Open},[Order Date]={"<$(=date(max([Order Date])-30))>=$(=date(max([Order Date])-60))"}>} Sales)