topic Re: Aggr() function in QlikView

Aggr() function

Mon, 16 Sep 2013 09:33:58 GMT

Hi guys,

I have a table as below:

yearid	custid	salesorder	amt
2012	A	1	15
2012	A	2	10
2012	A	3	16
2009	B	1	12
2010	B	2	15
2012	B	3	12
2013	B	4	14

And I use the aggr function to setup a expression to calculate the min salesorder group by custid,yearid,just like below:

yearid	custid	salesorder	amt	Min_Salesorder_Year
2012	A	1	15	1
2012	A	2	10	1
2012	A	3	16	1
2009	B	1	12	1
2010	B	2	15	2
2012	B	3	12	3
2013	B	4	14	4

And I want to have another expression to calculate the Min_Salesorder_Year-1 for Year-1,looks like:

yearid	custid	salesorder	amt	Min_Salesorder_Year	Min_Salesorder_Year-1
2012	A	1	15	1	-
2012	A	2	10	1	-
2012	A	3	16	1	-
2009	B	1	12	1	-
2010	B	2	15	2	1
2012	B	3	12	3	-
2013	B	4	14	4	3

Anyone has idea of how to write the expression about the "Min_Salesorder_Year-1"?

Thanks a lot.

Re: Aggr() function

Mon, 16 Sep 2013 10:02:39 GMT

For example,

yearid	custid	salesorder	amt	Min_Salesorder_Year	Min_Salesorder_Year-1
2009	B	1	12	1	-
2010	B	2	15	2	1

I need the value of Min_Salesorder_Year-1 in 2010 equivalent to Min_Salesorder_Year in 2009. It means the value 1 in green same as that in red.

Re: Aggr() function

Gysbert_Wassenaar — Mon, 16 Sep 2013 10:17:05 GMT

Try if(yearid-above(total yearid)=1,above(total [Min_Salesorder_Year]))

Re: Aggr() function

preminqlik — Mon, 16 Sep 2013 10:29:31 GMT

exactly

Re: Aggr() function

Mon, 16 Sep 2013 10:42:27 GMT

Thank you very much, it works.

Re: Aggr() function

Mon, 16 Sep 2013 11:54:48 GMT

Hi G Wassenaar,

First of all, thank you for your answer,actually I intend to use this expression as a calculated dimension to classify the customers, but when I put it into the calculated dimension ,only the aggr() dimension works.

What I want is, for example, a customer who bought in both 2012 and 2011(continuous two years) is considered as a existing customer,and a customer who bought in both 2012 and 2010(not continuous two years) is considered as a reactivated customer,a customer who only bought in current year is considered as a new customer.

And in this case, do you have any good ideas?