topic Re: How the expression sum the sales on max date ? in QlikView

How the expression sum the sales on max date ?

Wed, 17 Dec 2014 14:50:03 GMT

hi ,

I facing problem below:-

sales transaction table

salesperson, trx. date, trx amount

May, 20141006, 10.00

May, 20141006, 20.00

Lee, 20141106, 20.00

May, 20140906, 10.00

Santa, 20141206, 30.00

Lee, 20140106, 40.00

May, 20140506, 10.00

my expected result : -

Sales person Latest sales date Amount

May 20141006 30.00

Lee 20141106 20.00

Santa 20141206 30.00

How can get sum amount ? any idea ...

Re: How the expression sum the sales on max date ?

Wed, 17 Dec 2014 15:11:53 GMT

A large part of the problem you're facing is how you're loading in your dates. Your inline load isn't reading what you enter as a date. I assume this is just a sample app and isn't how you're actually loading in your data. The way you'd want to do it if you actually have a correctly formatted date field is:

straight table with sales person as a dimension

max(salesdate)

sum({<salesdate = ('=$(max(salesdate))')>}amount)

Re: How the expression sum the sales on max date ?

Gysbert_Wassenaar — Wed, 17 Dec 2014 19:56:07 GMT

FirstSortedValue(aggr(sum(fldamount),fldid,flddate) ,-aggr(flddate,flddate))

See attached qvw.

Re: How the expression sum the sales on max date ?

Wed, 17 Dec 2014 23:04:02 GMT

Thank you