Re: Complex expression with set analysis - can't a... - Qlik Community

Report Inappropriate Content · ‎2015-10-25

Hello everybody,

I have quite long (complex) expression which calculates sales based on two filter selections (Booking X Revenue, End User X Reported) for multiple fiscal years. I want to add condition for period quarter but Qlik Sense won't let me add this condition into every set analysis and I think I may have exceeded some limit for functions allowed in one expression.

I have a filter pane named Period Quarter containing values Q1, Q2, Q3 and Q4. Then I save the selected values into the variable:

varPeriodQuarterSelection = concat(distinct [Period Quarter],chr(39)&','&chr(39))

And I want to calculate measure using following expression:

if((varBooking=-1 and varRevenue=-1) or (varEndUser=-1 and varReported=-1) or

(varBooking=0 and varRevenue=0) or (varEndUser=0 and varReported=0)

//then

,0

//else

,if(varBooking=-1

//then

,if(varEndUser=-1

//then

,pick(match(ValueList('FY14','FY15','BP15','FY15Mng','FY15Best','FY15Worst')

,'FY14','FY15','BP15','FY15Mng','FY15Best','FY15Worst')

//FY14

,sum({<Type*={AO,FTSS,NC,NP,RN},

[Booking Quarter]*={'$(=varPeriodQuarterSelection)'}>}[PY.End User Price])

//FY15

,sum({<Type*={AO,FTSS,NC,NP,RN},

[SF.Booking Quarter]*={'$(=varPeriodQuarterSelection)'}>}[End User Formal Unweighted])

//BP15

,sum({<Category={LICENSING},Type*={AO,FTSS,NC,NP,RN},

[BP.Booking Quarter]*={'$(=varPeriodQuarterSelection)'}>}[BP.End User Price])

//FY15Mng

,sum({<Type*={AO,FTSS,NC,NP,RN},

[SF.Booking Quarter]*={'$(=varPeriodQuarterSelection)'}>}[End User Mng])

//FY15Best

,sum({<Type*={AO,FTSS,NC,NP,RN},

[SF.Booking Quarter]*={'$(=varPeriodQuarterSelection)'}>}[End User Best])

//FY15Worst

,sum({<Type*={AO,FTSS,NC,NP,RN},

[SF.Booking Quarter]*={'$(=varPeriodQuarterSelection)'}>}[End User Worst])

)

//else

,if(varReported=-1

//then

,pick(match(ValueList('FY14','FY15','BP15','FY15Mng','FY15Best','FY15Worst')

,'FY14','FY15','BP15','FY15Mng','FY15Best','FY15Worst')

//FY14

,sum({<Type*={AO,FTSS,NC,NP,RN},

[Booking Quarter]*={'$(=varPeriodQuarterSelection)'}>}[PY.Reported Price])

//FY15

,sum({<Type*={AO,FTSS,NC,NP,RN},

[SF.Booking Quarter]*={'$(=varPeriodQuarterSelection)'}>}[Reported Formal Unweighted])

//BP15

,sum({<Category={LICENSING},Type*={AO,FTSS,NC,NP,RN},

[BP.Booking Quarter]*={'$(=varPeriodQuarterSelection)'}>}[BP.Reported Price])

//FY15Mng

,sum({<Type*={AO,FTSS,NC,NP,RN},

[SF.Booking Quarter]*={'$(=varPeriodQuarterSelection)'}>}[Reported Mng])

//FY15Best

,sum({<Type*={AO,FTSS,NC,NP,RN},

[SF.Booking Quarter]*={'$(=varPeriodQuarterSelection)'}>}[Reported Best])

//FY15Worst

,sum({<Type*={AO,FTSS,NC,NP,RN},

[SF.Booking Quarter]*={'$(=varPeriodQuarterSelection)'}>}[Reported Worst])

)

))

//else

,if(varRevenue=-1

//then

,if(varEndUser=-1

//then

,pick(match(ValueList('FY14','FY15','BP15','FY15Mng','FY15Best','FY15Worst')

,'FY14','FY15','BP15','FY15Mng','FY15Best','FY15Worst')

//FY14

,sum({<[PY.Revenue Fiscal Year]={14},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN},

[Revenue Quarter]*={'$(=varPeriodQuarterSelection)'}>}[PY.End User Price])

+sum({<[Bg.Revenue Fiscal Year]={14},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN},

[Revenue Quarter]*={'$(=varPeriodQuarterSelection)'}>}[Bg.End User Price])

//FY15

,sum({<[Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN},

[Revenue Quarter]*={'$(=varPeriodQuarterSelection)'}>}[End User Formal Unweighted])

+sum({<[PY.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN},

[Revenue Quarter]*={'$(=varPeriodQuarterSelection)'}>}[PY.End User Price])

+sum({<[Bg.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN},

[Revenue Quarter]*={'$(=varPeriodQuarterSelection)'}>}[Bg.End User Price])

//BP15

,sum({<[BP.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN},

[Revenue Quarter]*={'$(=varPeriodQuarterSelection)'}>}[BP.End User Price])

//FY15Mng

,sum({<[Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN},

[Revenue Quarter]*={'$(=varPeriodQuarterSelection)'}>}[End User Mng])

+sum({<[PY.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN},

[Revenue Quarter]*={'$(=varPeriodQuarterSelection)'}>}[PY.End User Price])

+sum({<[Bg.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN},

[Revenue Quarter]*={'$(=varPeriodQuarterSelection)'}>}[Bg.End User Price])

//FY15Best

,sum({<[Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN},

[Revenue Quarter]*={'$(=varPeriodQuarterSelection)'}>}[End User Best])

+sum({<[PY.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[PY.End User Price])

+sum({<[Bg.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[Bg.End User Price])

//FY15Worst

,sum({<[Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[End User Worst])

+sum({<[PY.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[PY.End User Price])

+sum({<[Bg.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[Bg.End User Price])

)

//else

,if(varReported=-1

//then

,pick(match(ValueList('FY14','FY15','BP15','FY15Mng','FY15Best','FY15Worst')

,'FY14','FY15','BP15','FY15Mng','FY15Best','FY15Worst')

//FY14

,sum({<[PY.Revenue Fiscal Year]={14},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[PY.Reported Price])

+sum({<[Bg.Revenue Fiscal Year]={14},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[Bg.Reported Price]

//FY15

,sum({<[Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[Reported Formal Unweighted])

+sum({<[PY.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[PY.Reported Price])

+sum({<[Bg.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[Bg.Reported Price])

//BP15

,sum({<[BP.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[BP.Reported Price])

//FY15Mng

,sum({<[Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[Reported Mng])

+sum({<[PY.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[PY.Reported Price])

+sum({<[Bg.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[Bg.Reported Price])

//FY15Best

,sum({<[Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[Reported Best])

+sum({<[PY.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[PY.Reported Price])

+sum({<[Bg.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[Bg.Reported Price])

//FY15Worst

,sum({<[Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[Reported Worst])

+sum({<[PY.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[PY.Reported Price])

+sum({<[Bg.Revenue Fiscal Year]={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}[Bg.Reported Price])

)

)))))

I want to add [Revenue Quarter]*={'$(=varPeriodQuarterSelection)'} to all sums after the one highlighted in bold but Qlik Sense won't let me. If I try to copy the condition nothing happens.

I was thinking about making one chart for Booking only and second one for Revenue and using conditional show/hide based on selection but Qlik Sense does not have this functionality. Second solution would be putting the sums into variables but that would break my stacked bar charts.

Does anybody have any idea how to overcome this limitation or how to simplify the expression so I can add more conditions into set analysis?

Thank you in advance,

Martin

marcus_sommer · ‎2015-10-25

AFAIK there are only very few limitations (like not more then 99 nested if-loops) and I'm not sure if you have reached one. But I suggest very strongly to simplify your expression maybe with the using from (parametrizied) variables: The Magic of Variables and Re: Simplify the code if possible and/or some additionally pre-calculations (maybe some further flag-fields) within the script.

Further I see a lot of fix values into the conditions and many sum() + sum() by which one error led to an error for the whole expression - better is here to use rangesum() and/or alt() to catch possible errors.

Development, handling and maintaining from such long and complex expressions is a horror and needs at all more time then simplifying efforts.

- Marcus

Oleg_Troyansky · ‎2015-10-25

Hi Martin,

without reading ALL of your expressions and understanding all the various conditions that you are trying to epxress - there HAS GOT to be a simpler way to do it. I have a feeling that you don't completely grasp the meaning of Qlik Associative logic and you are trying to compensate for that with overly convoluted logic.

Even if you are not hitting any hard limitations on the length of the expression and the number of nested IF statements, you certainly exceeded the virtual limitation on manageability and maintainability of your logic. This is most likely the reason that won't let you add another condition there.

My practical recommendation would be to step back, reassess what are you trying to accomplish and find a simpler way of doing the same. If the logic is not too specific, I can give it a shot and guide you here. If it requires more than a few minutes of someone's time, then it crosses into consulting territory, and I'd be happy to connect with you individually.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView an...

Oleg Troyansky
Call me for expert support, performance tuning, advanced training and more!

Report Inappropriate Content · ‎2015-10-26

I am trying to simplify the expression using links provided by Marcus but I can't figure out how to create set analysis with parameters.

I have loaded some data which are used in filter pane:

LOAD * INLINE

[Period Quarter,

Q1,

Q2,

Q3,

Q4

];

I have defined some variables in load script:

Let vTypes = 'AO' & ',' & 'FTSS' & ',' & 'NC' & ',' & 'NP' & ',' & 'RN';

Let eBooking = 'sum({<[Type]*={$(vTypes)},$1*={$(=$2)}>}$3)';

and one variable in UI:

vPeriodSelection =chr(39)&concat(distinct [Period Quarter],chr(39)&','&chr(39))&chr(39) // this should give me e.g. 'Q1','Q2'

Now I want to pass vPeriodSelection as parameter $2 but I always get zero as result.

$(eBooking([Booking Quarter],vPeriodSelection,[End User Price]))

I suppose the dollar expansions are not correct. Can you help me out please? This is new concept for me and I am kind of lost.

It should work exactly the same as this expression:

sum({<Type*={AO,FTSS,NC,NP,RN},[Booking Quarter]*={$(=varPeriodSelection)}>}[End User Price])

marcus_sommer · ‎2015-10-26

Such changes should be done step by step and not several steps at the same time - otherwise to find out which change hasn't worked like expected would be difficult. Further I wouldn't create the variables within the script - this is general possible and might be sensible in the end but at this step it's only a further source of error - use instead the variable-editor. Here an example how such variable-part could look like:

eExp:

sum({<$1={15},Category={LICENSING},Type*={AO,FTSS,NC,NP,RN}>}$2)

....

rangesum($(eExp([Revenue Fiscal Year], [Reported Best])),

$(eExp([PY.Revenue Fiscal Year], [PY.Reported Price])),

$(eExp([Bg.Revenue Fiscal Year], [Bg.Reported Price])))

....

But I think this is only one part of possible changes. More important could be to pre-calculate some things within the script or how from troyansky suggested to change the data-model. For me it looked if there are some crosstable-structures within your datamodel and if some tables should be per concatenate/join/mapping merged together. Here Get started with developing qlik datamodels you will find many helpful postings to create datamodels in qlikview.

- Marcus

Report Inappropriate Content · ‎2015-10-26

Thank you very much, I'll look further into it.

Complex expression with set analysis - can't add more conditions