Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi
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))
Hi
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))
Excellent. This works. Thanks for your help.
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.
Thanks,
I also found that to have "PRODUCT" as a dimension it needs including in the AGGR Function, so:
Aggr (xxxxx, CUSTOMER,PRODUCT)