topic Re: Indirect Set Analysis on Rolling Period in QlikView

Indirect Set Analysis on Rolling Period

cicciput — Fri, 06 Mar 2015 00:37:15 GMT

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;

Re: Indirect Set Analysis on Rolling Period

sunny_talwar — Fri, 06 Mar 2015 03:57:59 GMT

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,

Re: Indirect Set Analysis on Rolling Period

sunny_talwar — Fri, 06 Mar 2015 03:59:42 GMT

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

Best,

Re: Indirect Set Analysis on Rolling Period

cicciput — Fri, 06 Mar 2015 09:55:22 GMT

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,

Re: Indirect Set Analysis on Rolling Period

sunny_talwar — Fri, 06 Mar 2015 10:37:49 GMT

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,

Re: Indirect Set Analysis on Rolling Period

cicciput — Fri, 06 Mar 2015 11:00:20 GMT

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

I will try it.

Re: Indirect Set Analysis on Rolling Period

sunny_talwar — Fri, 06 Mar 2015 11:04:31 GMT

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,

Re: Indirect Set Analysis on Rolling Period

cicciput — Fri, 06 Mar 2015 12:05:47 GMT

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

Re: Indirect Set Analysis on Rolling Period

sunny_talwar — Fri, 06 Mar 2015 13:18:09 GMT

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,

Re: Indirect Set Analysis on Rolling Period

cicciput — Mon, 09 Mar 2015 10:59:15 GMT

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.

Re: Indirect Set Analysis on Rolling Period

sunny_talwar — Mon, 09 Mar 2015 11:07:26 GMT

So its working now? I was working on it right now and was not sure how I would do it. I am glad that you have figured it out.

Re: Indirect Set Analysis on Rolling Period

cicciput — Mon, 09 Mar 2015 11:14:36 GMT

Yes, I'm asked to work on a monthly basis so on the real app I'll need to round it up but the concept is working.

Thanks for helping me to understand Set Analysis could not work in this situation.

Re: Indirect Set Analysis on Rolling Period

sunny_talwar — Mon, 09 Mar 2015 12:29:05 GMT

Not a problem my friend. I am glad I was able to guide you in the right direction.