topic Re: Set analysis with identifiers, operators and modifiers in QlikView

Set analysis with identifiers, operators and modifiers

Tue, 15 Oct 2013 11:45:42 GMT

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

Re: Set analysis with identifiers, operators and modifiers

Tue, 15 Oct 2013 11:56:14 GMT

Hi Quentin

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

Best regards

Chris

Re: Set analysis with identifiers, operators and modifiers

swuehl — Tue, 15 Oct 2013 11:56:30 GMT

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)))} >}

)

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

)

>} Distinct OpportunityCD)

Re: Set analysis with identifiers, operators and modifiers

Tue, 15 Oct 2013 12:05:41 GMT

It's working !

I undersand now why my current expression return anything !

Thanks a lot Swuehl for your explanation and your help !

Re: Set analysis with identifiers, operators and modifiers

Wed, 16 Oct 2013 12:16:48 GMT

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.

Re: Set analysis with identifiers, operators and modifiers

swuehl — Wed, 16 Oct 2013 12:26:58 GMT

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.

Re: Re: Set analysis with identifiers, operators and modifiers

Wed, 16 Oct 2013 14:54:21 GMT

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