topic Re: Problem with set analysis - max expression in QlikView

Problem with set analysis - max expression

sankar_ganesh — Thu, 20 Mar 2014 15:47:39 GMT

Product	Sales_Date	Sales
a	03/20/2014	10
a	03/20/2014	5
a	03/19/2014	20
a	03/18/2014	30
b	02/28/2014	40
b	02/28/2014	20
b	02/20/2014	50
b	02/10/2014	60

Hi, The above table is the example source data for my report...i need to find the sum of sales occured on the latest sales date of each product to obtain the result shown below..

Result table to be shown:

Product	SalesAt_LatestDate
a	15
b	60

sum({<Sales_Date={'$(=maxstring(Sales_Date))'}>}Sales)

i used the above expression but i get only the the first row given in the result table as the maxstring(Sales_Date) is evaluated for the whole data not for each product. Does anyone have any idea to resolve this issue i.e. to evaluate the max(sales_date) for each product ?

Re: Problem with set analysis - max expression

Gysbert_Wassenaar — Thu, 20 Mar 2014 15:55:47 GMT

sum(if(Sales_Date=aggr(nodistinct max(Sales_Date),Product),Sales))

Re: Problem with set analysis - max expression

Nicole-Smith — Thu, 20 Mar 2014 15:56:50 GMT

sum({<Sales_Date={'=Sales_Date=aggr(maxstring(Sales_Date),Product)'}>}Sales)

Example file also attached.

Re: Problem with set analysis - max expression

SunilChauhan — Thu, 20 Mar 2014 15:56:54 GMT

sum({<Sales_Date={$(=Max(Sales_Date))}>}Sales)

make sure date format is not text