topic Re: Reusable Calculation Rule and SET Analysis in QlikView

Reusable Calculation Rule and SET Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I will need certain calculation rules in my application:

e.g. if(Variable1=1 AND Field1='ABC', ValueField1, if(Variable2=1 And Field2=1, ValueField2),0)

This calculation will be used in tables and also I would like to use the same rule in SET Analysis when building charts. So I want to store this rule in a variable using the variable dialogue e.g. VariableCalc =if(Variable1=1 AND Field1='ABC', ValueField1, if(Variable2=1 And Field2=1, ValueField2),0)

If I now use the variable in a SET Analysis Expression it does not calculate. For instance I wrote:

Sum({<Field4=>VariableCalc) or Sum({<Field4=>$(VariableCalc)) ----> Should calculate the sum and ignore selected values in Field4

If I put the if Statement directly in the SET Analysis it calculates. The SET Analysis would then look like

Sum({<Field4=>if(Variable1=1 AND Field1='ABC', ValueField1, if(Variable2=1 And Field2=1, ValueField2),0))

My question is how do I have to store the calculation rules in order to reuse it in tables and charts and how do I correctly use them. I found a couple of documents dealing with SET Analysis and variables but most of them refer to use variables in the dimensions.

Thanks for your help!!!

Re: Reusable Calculation Rule and SET Analysis

Anonymous — Wed, 13 Jul 2016 10:57:43 GMT

try this Expression:

Sum({<Field4={'>$(=VariableCalc)'}>}Value)

Re: Reusable Calculation Rule and SET Analysis

Gysbert_Wassenaar — Wed, 13 Jul 2016 10:59:20 GMT

Missing curly brace perhaps: Sum({<Field4=>}$(VariableCalc))

Re: Reusable Calculation Rule and SET Analysis

Anonymous — Wed, 13 Jul 2016 11:03:02 GMT

Thanks for the hint with the curly brackets. I correctly set them. I just missed them in the post. So my SET Analysis using the variable would look like:

Sum({<Field4=>}VariableCalc)

But it is still not working 😞

Re: Reusable Calculation Rule and SET Analysis

Gysbert_Wassenaar — Wed, 13 Jul 2016 11:29:05 GMT

Can you post a small qlikview document with some example data that makes clear what you're trying to do?

Re: Reusable Calculation Rule and SET Analysis

Anonymous — Wed, 13 Jul 2016 11:34:26 GMT

Sorry, I have checked again with your proposal. And it now works fine. I had to take the = away in the variable definition and then using the $() around the variable in the expression.

Thank you for your help!!!

Marcel