Set analysis with identifiers, operators and modifiers

[#1]

Hello everyone,

I have a question about set analysis. I cannot make intersection by nesting "identifiers" of "operators" and "Modifiers"

For context, i have:

July and 2013 selected.

Expression of « juil. 2013 » is a set analysis :

Count({$<Month={$(=Only(Month(MonthName(PRJ_ReloaDate))))}, Year={$(=Only(Year(PRJ_ReloaDate)))}>} Distinct OpportunityCD)

Which counts opportunities I have in July 2013 (my selection)

Expression of « juin 2013 » is a set analysis :

count({$<Month = {$(=only(Month(AddMonths(PRJ_ReloaDate,-1))))}, Year={$(=only(Year(AddMonths(PRJ_ReloaDate,-1))))}>} Distinct OpportunityCD)

Which count opportunities i have last month, taking into account my selection (July 2013)

About my intersection, I wish to mention those which are both in "July 2013" and "June 2013" with operator "*"

Count({$<Month={$(=Only(Month(MonthName(PRJ_ReloaDate))))}*{$(=only(Month(AddMonths(PRJ_ReloaDate,-1))))}, year={$(=Only(Year(PRJ_ReloaDate)))}*{$(=only(Year(AddMonths(PRJ_ReloaDate,-1))))}>} Distinct OpportunityCD)

Unfortunately, it does not work... Could you help me ?

Thanks a lot

Quentin

[#2]

Hi Quentin

Can't you simply use [column(3) * column(4)] as Intersection ?

Best regards

Chris

[swuehl]

Your current expression will try to intersect e.g. for Month {July} with {June}, which of course will not return anything.

You probably want to intersect value of OpportunityCD instead, maybe like

Count({$<

OpportunityCD = p(

{<Month={$(=Only(Month(MonthName(PRJ_ReloaDate))))}, year={$(=Only(Year(PRJ_ReloaDate)))} >}

)

*

p(

{<Month={$(=only(Month(AddMonths(PRJ_ReloaDate,-1))))}, year={$(=only(Year(AddMonths(PRJ_ReloaDate,-1))))} >}

)

>} Distinct OpportunityCD)

[#4]

It's working !

I undersand now why my current expression return anything !

Thanks a lot Swuehl for your explanation and your help !

[#5]

All works except January 2013: i've got January 2013 (676) and December 2012 (646) and "Old opportunities" (January 2013*December 2012)=0

It's not possible:

Normaly, intersection of January 2013 and December 2012 for opportunityCD "1750" should count 1. At the opposite, sysmetric difference:

Count({$<
OpportunityCD = p(
{<Month={$(=Only(Month(MonthName(PRJ_ReloaDate))))}, year={$(=Only(Year(PRJ_ReloaDate)))} >}
)
/
p(
{<Month={$(=only(Month(AddMonths(PRJ_ReloaDate,-1))))}, year={$(=only(Year(AddMonths(PRJ_ReloaDate,-1))))} >}
)

>} Distinct OpportunityCD)

is equal to January 2013...

I keep searching why. If you have any tips...

Thanks !

Quentin.

[swuehl]

Not easy to understand.

But if the issue arise only on year change, I would suspect that something breaks in the dollar sign expansions for the prev. Month set expression.

Try evaluating

=only(Month(AddMonths(PRJ_ReloaDate,-1)))

and

=only(Year(AddMonths(PRJ_ReloaDate,-1)))

in e.g. text boxes.

Furthermore, it might be better to create a continuous month field using Monthname() or monthstart() and use this in your set analysis, instead of needing to cope with a combination of two fields.

[#7 · Accepted solution]

Hi Swuehl,

I found the solution:

Count({$<
OpportunityCD = p(
{<Month={$(=Only(Month(MonthName(PRJ_ReloaDate))))}, year={$(=Only(Year(PRJ_ReloaDate)))} >}
)
*
p(
{1<Month={$(=only(Month(AddMonths(PRJ_ReloaDate,-1))))}, year={$(=only(Year(AddMonths(PRJ_ReloaDate,-1))))} >}
)

>} Distinct OpportunityCD)

It was the second indirect set analysis which caused the error:

p(
{<Month={$(=only(Month(AddMonths(PRJ_ReloaDate,-1))))}, year={$(=only(Year(AddMonths(PRJ_ReloaDate,-1))))} >}
)

Thanks to this website: http://tools.qlikblog.at/SetAnalysisWizard/Set-Analysis-Example--Indirect-Set-Analysis.aspx

"1" represent the full set of all the records in the application, this is why link was broken in my table Month with table Year. (When the set to modify is omitted, $ is assumed !)

Thanks again for your tips and your help.

Quentin