topic Re: Aggr with Two parameter Variable not working in QlikView

Aggr with Two parameter Variable not working

Tue, 25 Oct 2016 07:21:06 GMT

Hi All,

We have a scenario for Numerator and Denominator calculation. I have to consider some filters for Numerator and Denominator calculation.

One more condition we need to consider while calculating Numerator and Denominator which We have to look at Numerator and Denominator for each "MonthYear" and "Category" value should be '>' Zero, then only we have to take Numerator and Denominator for that particular "MonthYear" and "Category".

PFA .qvw and Data. We have used Aggr with SetAnalysis the expression is working fine.

The same way We have 100 No.of expressions. So we planned to use variable with two parameters. but it is now working.

Please help me to rectify the syntax problem.

Thanks in Advance,

Saranya.A

Re: Aggr with Two parameter Variable not working

Aurelien_Martinez — Tue, 25 Oct 2016 07:57:37 GMT

Hi,

You can add an '=' to this variable : eNumerator, like

=sum({<SubType1={'X','Y'}>}Measure1)

Aurélien

edit : same thing for eDenominator

Re: Aggr with Two parameter Variable not working

Tue, 25 Oct 2016 08:03:36 GMT

Thank you for the support Aurelien.

I have tried this, but actually I have to get the Expected result for Numerator is 640326 and Denominator is 35.

Then only my variable with parameter is correct.

Please suggest me to work on two parameter variable.

Thanks,

Saranya A.

Re: Aggr with Two parameter Variable not working

Aurelien_Martinez — Tue, 25 Oct 2016 08:22:56 GMT

When you i do that

sum({<SubType1={'X'}>}Measure1) + sum({<SubType1={'Y'}>}Measure1)

it's working

The problem is in the ',' in set analysis...

Re: Aggr with Two parameter Variable not working

Tue, 25 Oct 2016 09:24:46 GMT

Thank you very much Aurelin.

It is meeting my requirement. Actually, I have shared sample expression. In real scenario we have 4 to 5 no.of modifiers, each modifier have multiple values. So splitting it for 100 expression is difficult task.

Is there any other way to acheive?

Thanks in Advance,

Saranya A.

Re: Aggr with Two parameter Variable not working

marcus_sommer — Tue, 25 Oct 2016 11:47:10 GMT

Take a look here: Re: variable with comma parameter.

- Marcus

Re: Aggr with Two parameter Variable not working

Tue, 25 Oct 2016 13:49:02 GMT

Hi Marcus,

Thanks for your reply.

Now I understood the ',' is creating problem. But could not fix the issue.

Will please suggest me how to implement it?

Thanks in Advance,

Saranya

Re: Aggr with Two parameter Variable not working

marcus_sommer — Tue, 25 Oct 2016 16:45:31 GMT

If your parameter which is another variable which include a whole expression could be globally calculated you could do something like this: Nesting Variables with parameters. But must be those expression calculate the results depending on certain dimensions this way isn't possible. In short I don't think that there is a way to nest such variables if they must be expanded in eachother.

An alternatively would be to do something like this as variable:

Sum(Aggr(if((

Sum({<$1={$2}>}$3))>0 and

(sum({<$4={'$(=replace($5, '+', chr(39) & chr(44) & chr(39)))'}>}$6))>0,(sum({<$4={'$(=replace($5, '+', chr(39) & chr(44) & chr(39)))'}>}$6)),0),MonthYear,Category))

and this as expression:

=$(eparameterVar_NrNew(Type, 'A', Measure2, SubType1, 'X+Y', Measure1))

to get:

- Marcus

Re: Aggr with Two parameter Variable not working

Wed, 26 Oct 2016 05:10:56 GMT

Thank you very much Marcus,

I will try to implement the same concept into my application. In my case, passing parameter will differ for each and every expression, so i need to create multiple variables, to avoid these I will go for direct expression method only, instead of parameter with variables.

Thanks,

Saranya A.

Re: Aggr with Two parameter Variable not working

Wed, 26 Oct 2016 07:16:32 GMT

Thank you very much for all your support.

We have tried it in double dollar expansion format. Now it is working fine.

PFA which is udated with double dollar expansion variable with two parameters.

Thanks,

Saranya A.