topic Re: Help set analysis in QlikView

Help set analysis

Anonymous — Thu, 12 Jul 2012 15:12:08 GMT

Hello everybody,

I'm trying to build a function that I pull out, per customer, the last date on which he purchased, and on that date, the item purchased with the highest economic value. That is the record with the highest value of the last sale per customer.

I wrote something like this, but it doesn't work.

sum({<DATA_DDT={$( aggr(max(DATA_DDT),C_CLIENTE))}>} aggr(max({<DDT_SN={'S'}>}VAL_DDT),C_CLIENTE,C_ARTICOLO))

DATA_DDT is the date

C_CLIENTE is the customer code

C_ARTICOLO is the article code

VAL_DDT is the article value €

DDT_SN is only a flag to reduce data, it's not important

Can you help me?

Re: Help set analysis

Sat, 14 Jul 2012 17:06:59 GMT

You could probably try something like this sample.

Re: Help set analysis

nagaiank — Sat, 14 Jul 2012 18:33:53 GMT

The above result is produced by the attached application. Is this what is needed? (The data used in this application is the same as that used by beanz above

Message was edited by: Nagaian Krishnamoorthy

Re: Help set analysis

chematos — Sat, 14 Jul 2012 21:04:05 GMT

I think that could be better to do it in the load script.

Try this:

MaxSales:

Load

Customer,

max(Date) as MaxDate,

FirstSortedValue(distinct SalesAmount, -Date) as MaxSalesAmountDate

from TABLEX

group by Customer;

hope this helps

Re: Help set analysis

Anonymous — Fri, 10 Aug 2012 13:04:36 GMT

Thanks to everybody but nothing you suggest me works...

Re: Help set analysis

chematos — Fri, 10 Aug 2012 14:10:18 GMT

MaxSales:

Load

C_CLIENTE ,

C_ARTICOLO ,

max(DATA_DDT) as MaxDate,

FirstSortedValue(distinct VAL_DDT, -DATA_DDT) as MaxSalesAmountDate

from TABLEX

group by C_CLIENTE , C_ARTICOLO ;