Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for
Did you mean:
Contributor III

## Indirect Set Analysis on Rolling Period

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)

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:

Account.ID, Account.Name, Account.DCF

1, Company A, 1

2, Company B, 1

3, Company C, 1

];

Purchase:

Account.ID, Date

1, 27/02/2015

2, 20/02/2015

2, 20/11/2014

3, 10/10/2014

];

Cal:

TempDate AS Date,

MonthName(TempDate) AS Cal.MonthName;

Date(mindate + IterNo()) AS TempDate

WHILE mindate + IterNo() <= maxdate;

Min(Date) -1 AS mindate,

Max(Date) AS maxdate

RESIDENT Purchase;

AsOfMonth:

MonthName(AddMonths(Cal.MonthName, 1 - IterNo())) AS Cal.MonthName,

IF(IterNo() < 4, 'RQ', 'PRQ') AS AsOfMonth.Type

RESIDENT Cal

WHILE IterNo() <= 6;

1 Solution

Accepted Solutions
MVP

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

12 Replies
MVP

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

MVP

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

Best,

S

Contributor III
Author

Hello sunindia,

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,

MVP

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

Contributor III
Author

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

I will try it.

MVP

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

Contributor III
Author

Let's add a couple of sales to be sure:

Account:

Account.ID, Account.Name, Account.DCF

1, Company A, 1

2, Company B, 1

3, Company C, 1

4, Company D, 1

];

Purchase:

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.

MVP

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

Contributor III
Author

Hello,

here what I ended up making calculations on LOAD instead of using only Set Analysis:

Account:

Account.ID, Account.Name, Account.DCF

1, Company A, 1

2, Company B, 1

3, Company C, 1

4, Company D, 1

];

Purchase:

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:

Date(StartDate + IterNo() - 1) AS Date,

'Churn' AS Type

WHILE StartDate + IterNo() - 1 <= EndDate;

RangeMin(

IF(Account.ID = Previous(Account.ID), Previous(Date) -1, Null()),

Today(),

) AS EndDate

RESIDENT Purchase

ORDER BY Account.ID, Date desc;

CONCATENATE(Activity)

Date,

'Purchase' AS Type

RESIDENT Purchase;

DROP TABLE Purchase;

Cal:

TempDate AS Date,

MonthName(TempDate) AS Cal.MonthName;

Date(mindate + IterNo()) AS TempDate

WHILE mindate + IterNo() <= maxdate;