Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Customer Retention Rates - Sorting within AGGR() (or any other solution appreciated)

Hi,

I have what seems to be a not-so-simple request.

Data Model:

For the purposes of this discussion, let us assume I have 3 dimensions:

     DIM_DATE where the key is %txn_date_sk    (for transaction date key). This is a full master calendar.

     DIM_CUSTOMER where the key is %customer_sk

     DIM_ABC where the key is %abc (the value of this is not important, but I want to show that i can have 2 rows for the same day and same customer).

Then I have a Fact table which contains a number of measures:

FACT_REVENUE which is linked to the above 3 dimensions (and a number of others). Let us say that FACT_REVENUE has two main measures:

Money_Put_In

Money_Taken_Out.

There cannot be a row where both Money_Put_In and Money_Taken_Out are > 0.

Problem

Now the question I am trying to answer is:

Out of the customers who have sum(Money_Put_In) > 0 in Month X - 1, how many have sum(Money_Put_In) > 0 in Month X. The result I'm looking for looks something like:

Jan 2013 - 70%

Feb 2013 - 75%

Mar 2013 - 60 %

etc.

So I cannot use set analysis, as that only gives me for the previous month "hardcoded" in the selection.

I managed to get the data in the form of:

customer_sk | Dim_Date.MonthYear | IsActiveInNextMonth

1 | Jan-2013 | Yes

1 | Feb-2013 | No

2 | Jan-2013 | Yes

...

I achieve this by putting in the SUM(Money_Put_In) in a straight table with customer_sk and Dim_Date.MonthYear, sorted by customer_sk and Dim_Date.MonthYear. Then I use the ABOVE function to make row-wise comparisons.

My plan was to use this method in an AGGR(), but I cannot figure out how to explicitly state what the sorting needs to be within the Aggregation.

Any help on this? Is there a better method which does not involve pre-calculating in a script?

Thanks,

Robert

11 Replies
luciancotea
Specialist
Specialist

In expression:

1) you group DISTINCT Customer&Month, by current and previous months

2) if only() is null, then you have the customer in both months. Obs: isnull(only()) returns true (-1) , therefore we have a - (minus) in front of sum().

3) divide it by customers in previous month

Not applicable
Author

Thanks for the explanation. I have a fun weekend implementing this on my model now