I cannot understand what's happening here, I wonder if some expert can shine a light on this thing.
I want to compare buyers of a quarter VS the previous one.
To get there I'm using Indirect Set Analysis with 2 AsOfMonth.Type: RQ (Rolling Quarter) and PRQ (Previuos Rolling Quarter)
With no selections this expression returns no results or always 0:
Sum({$< Account.ID =
p({$< AsOfMonth.Type = {'PRQ'} >} Account.ID) -
p({$< AsOfMonth.Type = {'RQ'} >} Account.ID) >}
Account.DCF)
while it works if I select a single month:
Here are the LOAD statements for who's willing to help me understand, thanks in advance for your support.
Account:
LOAD * INLINE [
Account.ID, Account.Name, Account.DCF
1, Company A, 1
2, Company B, 1
3, Company C, 1
];
Purchase:
LOAD * INLINE [
Account.ID, Date
1, 27/02/2015
2, 20/02/2015
2, 20/11/2014
3, 10/10/2014
];
Cal:
LOAD
TempDate AS Date,
MonthName(TempDate) AS Cal.MonthName;
LOAD
Date(mindate + IterNo()) AS TempDate
WHILE mindate + IterNo() <= maxdate;
LOAD
Min(Date) -1 AS mindate,
Max(Date) AS maxdate
RESIDENT Purchase;
AsOfMonth:
LOAD Cal.MonthName AS AsOfMonth.MonthName,
MonthName(AddMonths(Cal.MonthName, 1 - IterNo())) AS Cal.MonthName,
IF(IterNo() < 4, 'RQ', 'PRQ') AS AsOfMonth.Type
RESIDENT Cal
WHILE IterNo() <= 6;
Accepted Solutions
I think the issue is that the set analysis is evaluated at the chart level and not the row level so when nothing is selected this is what you have:
Sum({$< Account.ID = p({$< AsOfMonth.Type = {'PRQ'} >} Account.ID) - p({$< AsOfMonth.Type = {'RQ'} >} Account.ID) >}
Account.DCF) -> Sum({$<Account.ID = {2,3} - {1,2,3} Account.DCF) -> Sum({$<Account.ID = {Nothing} Account.DCF) (The negative sign here means set of records from set A but not from B)
This expression is just going to give you 0's for each row.
But when you make a selection, lets say on Feb-2015 your set analysis statement changes to this
Sum({$< Account.ID =
p({$< AsOfMonth.Type = {'PRQ'} >} Account.ID) -
p({$< AsOfMonth.Type = {'RQ'} >} Account.ID) >}
Account.DCF) -> Sum({$<Account.ID = {2,3} - {1,2} Account.DCF -> Sum({$<Account.ID = {2} Account.DCF) = 1
Now I am not sure what exactly you are trying to achieve but if you want to get the intersection of the two you might want to use *
Sum({$< Account.ID =
p({$< AsOfMonth.Type = {'PRQ'} >} Account.ID) *
p({$< AsOfMonth.Type = {'RQ'} >} Account.ID) >}
Account.DCF) -> -> Sum({$<Account.ID = {2,3} * {1,2,3} Account.DCF) -> Sum({$<Account.ID = {2,3} Account.DCF)
The output looks like this
or if you want the opposite of the intersection, you will have to use /
Sum({$< Account.ID =
p({$< AsOfMonth.Type = {'PRQ'} >} Account.ID) /
p({$< AsOfMonth.Type = {'RQ'} >} Account.ID) >}
Account.DCF) -> -> Sum({$<Account.ID = {2,3} / {1,2,3} Account.DCF) -> Sum({$<Account.ID = {1} Account.DCF)
The output looks like this
Also find attached the application.
HTH
Best,
S
You might find this website helpful in understanding the concept of +,-,*,/ just like I did today to solve your issue. 
Best,
S
Hello sunindia,
thanks for you reply.
What I want to achieve is really exclusion and I supposed - is the right operator:
Find how many buyers purchesed in PRQ but not in RQ, for each month
I want to count the cyan part, per month.
Somehow I don't understand what happens,
Let's add a couple of sales to be sure:
Account:
LOAD * INLINE [
Account.ID, Account.Name, Account.DCF
1, Company A, 1
2, Company B, 1
3, Company C, 1
4, Company D, 1
];
Purchase:
LOAD * INLINE [
Account.ID, Date
1, 27/02/2015
2, 20/02/2015
2, 20/11/2014
3, 10/10/2014
2, 25/09/2014
3, 05/09/2014
4, 01/08/2014
];
Here the set operations as should be, along with the result I need:
Feb 2015 {2,3} - {2,1} = {3} >>> 1
Jan 2015 {2,3,4} - {2} = {3,4} >>> 2
Dec 2014 {2,3,4} - {2,3} = {4} >>> 1
Nov 2014 {4} - {3,2} = {4} >>> 1
Also find attached sample app.
Thanks for your support
I have few meetings in the morning today, but I promise that if no one else helps, I will be more than happy to put my time in helping you out. But lets hope that someone can come in and help you out sooner than that
Best,
S
Hello,
here what I ended up making calculations on LOAD instead of using only Set Analysis:
Account:
LOAD * INLINE [
Account.ID, Account.Name, Account.DCF
1, Company A, 1
2, Company B, 1
3, Company C, 1
4, Company D, 1
];
Purchase:
LOAD * INLINE [
Account.ID, Date
1, 27/02/2015
2, 20/02/2015
2, 10/11/2014
3, 20/10/2014
2, 25/09/2014
3, 05/09/2014
4, 01/08/2014
];
Activity:
LOAD Account.ID,
Date(StartDate + IterNo() - 1) AS Date,
'Churn' AS Type
WHILE StartDate + IterNo() - 1 <= EndDate;
LOAD Account.ID,
AddMonths(Date, 3) AS StartDate,
RangeMin(
IF(Account.ID = Previous(Account.ID), Previous(Date) -1, Null()),
Today(),
AddMonths(Date, 6) - 1
) AS EndDate
RESIDENT Purchase
ORDER BY Account.ID, Date desc;
CONCATENATE(Activity)
LOAD Account.ID,
Date,
'Purchase' AS Type
RESIDENT Purchase;
DROP TABLE Purchase;
Cal:
LOAD
TempDate AS Date,
MonthName(TempDate) AS Cal.MonthName;
LOAD
Date(mindate + IterNo()) AS TempDate
WHILE mindate + IterNo() <= maxdate;
LOAD
Min(Date) -1 AS mindate,
Max(Date) AS maxdate
RESIDENT Activity;
the result here is on a daily basis while I should consider rounding down on a monthly basis with only a record per month per buyer to reduce memory footprint but that's another topic.
Test file attached.
