Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Pick match or Set Analysis

Dear Qlikview user

I have a button called Type and when the user clicks on it, ,,y expression is designed to show the relevant value. However my expression is using an If statement and I would like to try and use set analysis instead, so it only evaluates the required data

My Button is called "Type"

My expression is as follows:

If(TYPE = 'INCOMPLETE',

max({<Month,QuarterName,FiscalYear,FactDate={'$(=$(e_RTT_Max_Date))'}>}DAYS_WAIT)/7

,

If(TYPE = 'ADM' OR TYPE = 'NON ADM',

max({<Month,QuarterName,FiscalYear,MonthYear={'$(=$(e_RTT_Max_Month))'}>}DAYS_WAIT)/7

))

I know Pick(Match is better than and If, but I am not sure how I would better that as I want to use set analysis instead

Any help would be greatly appreciated

Thanks

Helen

1 Solution

Accepted Solutions
Highlighted
Master
Master

Re: Pick match or Set Analysis

Hi Helen,

               I don't think further set analysis is appropriate. Let's take a step back and think about the purpose of set analysis. Set analysis allows us to calculate expressions based on the records we choose independent of those associated with the current selected dimension values.

We want our choice of expression (either of the two expressions in the if statement) to be based on the user's selection of TYPE so there's no point is writing a piece of set analysis to evade the user's choice of TYPE.

If we are looking at a subset of the records associated with the current selections we can do this in different ways. We can use such expressions as Sum(if(Director <>'Ingmar Bergman', [Box Office Takings]).


In this case rewriting as Sum({$<Director -= {'Ingmar Bergman}>}[Box Office Takings]) is preferred for performance and awareness of this is probably why the edict you've been asked to work to was proposed.


An if statement inside an expression can be improved by re-writing it using set analysis but in your example it's the other way round - we have two expressions inside an if statement so it's a different situation.


Kind regards


Andrew


View solution in original post

6 Replies
Highlighted

Re: Pick match or Set Analysis

Does If...else condition getting any performance issues for you? I don't think this equation kills the performance.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator II
Creator II

Re: Pick match or Set Analysis

Hi,

Yes we are trying to get way from all If statements if necessary and try and use set analysis as much as possible, but am unsure how to fit this expression into Set analysis

Kind Regards

Helen

Highlighted
Master
Master

Re: Pick match or Set Analysis

Hi Helen,

If Type can only be 'ADM', 'NON ADM' or 'INCOMPLETE' then we can simplify your expression to:

If(TYPE = 'INCOMPLETE',

max({<Month,QuarterName,FiscalYear,FactDate={'$(=$(e_RTT_Max_Date))'}>}DAYS_WAIT)/7,

max({<Month,QuarterName,FiscalYear,MonthYear={'$(=$(e_RTT_Max_Month))'}>}DAYS_WAIT)/7)

I much prefer Pick(Match(...)) to nested if statements but if you can simplify to the above then the if statement is fine. I don't think any more set analysis would improve things.


Cheers


Andrew

Highlighted
Creator II
Creator II

Re: Pick match or Set Analysis

Hello Andrew

Thank you for your suggestion...It works and nice to know that it is OK to use an If statement

However, what if set analysis was insisted upon, how could I adapt the If statement to become set analysis

Kind Regards

Helen

Highlighted
Master
Master

Re: Pick match or Set Analysis

Hi Helen,

               I don't think further set analysis is appropriate. Let's take a step back and think about the purpose of set analysis. Set analysis allows us to calculate expressions based on the records we choose independent of those associated with the current selected dimension values.

We want our choice of expression (either of the two expressions in the if statement) to be based on the user's selection of TYPE so there's no point is writing a piece of set analysis to evade the user's choice of TYPE.

If we are looking at a subset of the records associated with the current selections we can do this in different ways. We can use such expressions as Sum(if(Director <>'Ingmar Bergman', [Box Office Takings]).


In this case rewriting as Sum({$<Director -= {'Ingmar Bergman}>}[Box Office Takings]) is preferred for performance and awareness of this is probably why the edict you've been asked to work to was proposed.


An if statement inside an expression can be improved by re-writing it using set analysis but in your example it's the other way round - we have two expressions inside an if statement so it's a different situation.


Kind regards


Andrew


View solution in original post

Highlighted
Creator II
Creator II

Re: Pick match or Set Analysis

Hello Andrew

Thank you for spending the time to answer my question. It makes sense now and really appreciate your explanation

Kind Regards

Helen