topic Re: Aging dates based on selected date in list box selection in QlikView

Aging dates based on selected date in list box selection

Sun, 02 Aug 2015 13:24:51 GMT

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

Re: Aging dates based on selected date in list box selection

marcus_sommer — Sun, 02 Aug 2015 13:39:15 GMT

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

Re: Aging dates based on selected date in list box selection

Kushal_Chawda — Sun, 02 Aug 2015 13:50:59 GMT

Please see the attached

Re: Aging dates based on selected date in list box selection

Sun, 02 Aug 2015 13:56:13 GMT

thanku bro ur perfect

Re: Aging dates based on selected date in list box selection

Kushal_Chawda — Sun, 02 Aug 2015 13:58:47 GMT

Glad that I was able to help

Re: Aging dates based on selected date in list box selection

maxgro — Sun, 02 Aug 2015 14:00:28 GMT

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)