Indirect Set Analysis on Rolling Period

[cicciput]

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;

[sunny_talwar]

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

[sunny_talwar]

You might find this website helpful in understanding the concept of +,-,*,/ just like I did today to solve your issue.

Best,

S

[cicciput]

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,

[sunny_talwar · Accepted solution]

If this is what you want to do, then I think the only option left for you is to calculate this in the script itself which will give u the output on a row level. Have you thought about doing it in the script??

Best,

S

[cicciput]

No, because I thought it was possible with indirect set analysis.

I will try it.

[sunny_talwar]

Can you put down the output you are expecting from the data you have shared??  I can also work towards getting a solution for you.

May be this?

Feb2015 - 1

Jan2015 - 1

Dec2014 - 0

Nov2014 - 0

Oct2014 - 0

Best,

S

[cicciput]

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

[sunny_talwar]

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

[cicciput]

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.