Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Set analysis with identifiers, operators and modifiers

Hello everyone,

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

For context, i have:

2013-10-15_12h23_05.png2013-10-15_12h36_24.png

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

1 Solution

Accepted Solutions
Highlighted
Not applicable

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

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

View solution in original post

6 Replies
Highlighted
Not applicable

Re: Set analysis with identifiers, operators and modifiers

Hi Quentin

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

Best regards

Chris

Highlighted
MVP
MVP

Re: Set analysis with identifiers, operators and modifiers

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)

Highlighted
Not applicable

Re: Set analysis with identifiers, operators and modifiers

It's working !

I undersand now why my current expression return anything !

Thanks a lot Swuehl for your explanation and your help !

Highlighted
Not applicable

Re: Set analysis with identifiers, operators and modifiers

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:

2013-10-16_14h04_17.png

2013-10-16_14h10_45.png

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.

Highlighted
MVP
MVP

Re: Set analysis with identifiers, operators and modifiers

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.

Highlighted
Not applicable

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

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

View solution in original post