Advanced Aggr function help

Wed, 27 Jun 2012 11:19:24 GMT

Hi, I have a dataset at the moment which I create in Qlik, then extract to Excel for further calculation. I am sure it can all be done in Qlik, but need assistance to work out how.

PROBLEM DESCRIPTION:

I have a Base table with the following fields:

CUSTOMER

PRODUCT

Year

Month

SALES_REVENUE

I Use a straight Table (dimensions CUSTOMER & PRODUCT) to show which Customers bought in different years:

CUSTOMER	PRODUCT	Sales Revenue 2008	2008Flag	Sales Revenue 2009	2009Flag
CUSTOMER	PRODUCT	SUM({<Year={2008}>}SALES_REVENUE)	IF(SUM({<Year={2008}>}SALES_REVENUE)=0,0,1)	SUM({<Year={2009>}SALES_REVENUE)	IF(SUM({<Year={2009}>}SALES_REVENUE)=0,0,1)
A	1	100	1	200	1
A	2	50	1	0	0
B	1	0	0	100	1
B	2	0	0	0	0
B	3	75	1	75	1

Then I take the data to Excel and filter the flag columns to show, per product, how much Revenue is from Lost, New or continuing customers ("Customer Base") - without showing the customer as a dimension

(eg Customer A bought Product 2 for $50 in 2008, but $0 in 2009. They are lost customer. Their Revenue from 2008 should show in the "Lost Customers" field below for Product 2.:

	Sales Revenue
	Lost Customers	Customer Base	New Customers
PRODUCT	IF(2008Flag = 1 AND 2009Flag=0), SUM(Sales Revenue 2008),0)	IF(2008Flag = 1 AND 2009Flag=1), SUM(Sales Revenue 2009),0)	IF(2008Flag = 0 AND 2009Flag=1), SUM(Sales Revenue 2009),0)
1	0	200	100
2	50	0	0
3	0	75	0

I do the same with a Count of customers to work out how many customers are "Lost", "New" or "Base".

I want to do all of this as one step in a Straight Table.

If I keep CUSTOMER & PRODUCT as dimensions I can get the data to show correctly.

E.g. Lost Customer Revenue code:


IF(SUM({<Year={2008}>}SALES_REVENUE)>0 AND SUM({<Year={2009}>}SALES_REVENUE)<=0,
 SUM({<Year={2008}>}SALES_REVENUE),0)

But if I remove the CUSTOMER field as a dimension the calculation is a sum of all revenue for 2008 vs 2009, not "per customer".

Therefore I need an "Aggr" function.

I have tried:

IF 
(Aggr(SUM({<Year={2008}>}SALES_REVENUE),CUSTOMER)>0 AND Aggr(SUM({<Year={2009}>}SALES_REVENUE),CUSTOMER)<=0,
 Aggr(SUM({<Year={2008}>}SALES_REVENUE),CUSTOMER),0)

Also tried:


IF (Aggr(SUM(Total {<Year={2008}>}SALES_REVENUE),CUSTOMER,Year)>0 AND Aggr(SUM(Total {<Year={2009}>}SALES_REVENUE),CUSTOMER,Year)<=0,
Aggr(SUM(Total {<Year={2008}>}SALES_REVENUE),CUSTOMER,Year),0)

I think I need an "Aggr" function around the whole IF statement.

Does anyone have any ideas?

Re: Advanced Aggr function help

perumal_41 — Wed, 27 Jun 2012 12:14:46 GMT

Try This Expression .for helpful for u

Sum(Aggr(if(SUM({<Year={2008}>}SALES_REVENUE)>0 AND SUM({<Year={2009}>}SALES_REVENUE)<=0, SUM({<Year={2008}>}SALES_REVENUE)),CUSTOMER))

Re: Advanced Aggr function help

Wed, 27 Jun 2012 12:23:13 GMT

Excellent. This works. Thanks for your help.

Re: Advanced Aggr function help

Wed, 27 Jun 2012 12:31:16 GMT

Hi Ricki

This one should work for LOST

Sum

     (
          Aggr(
               IF(
                    SUM({<Year={2008}>}SALES_REVENUE)>0 AND SUM({<Year={2009}>}SALES_REVENUE)<=0
                         ,SUM({<Year={2008}>}SALES_REVENUE)
                         ,0
               )
               ,CUSTOMER

)
)

Remember that the AGGR function is really just a method of building an array of values, so putting multiple AGGR statements together in a single expression really doesn't work because each is evaluated on its own.

Good luck,

Nigel.

Re: Advanced Aggr function help

Wed, 27 Jun 2012 13:05:01 GMT

Thanks,

I also found that to have "PRODUCT" as a dimension it needs including in the AGGR Function, so:

Aggr (xxxxx, CUSTOMER,PRODUCT)

topic Re: Advanced Aggr function help in QlikView

Advanced Aggr function help

Re: Advanced Aggr function help

Re: Advanced Aggr function help

Re: Advanced Aggr function help

Re: Advanced Aggr function help