Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Thanks for the explanation. I have a fun weekend implementing this on my model now