topic Re: need quick help with set analysis in QlikView

need quick help with set analysis

praveensp — Sat, 16 Nov 2024 05:12:37 GMT

Hi Guys,

I need help in applying set analysis.

I have to do calculation of percentage. I have already applied a set analysis for denominator. I need to again apply a set analysis for the whole calculation i.e., numerator and denominator both. Kindly help me to resolve this problem.

Below is my formula -

=count(proficy_ptn) / Count({$<[Motor Group]={TM1},[WAG]={WAG2}>}[Motor Group])+count({$<[Motor Group]={TM2},[WAG]={WAG2}>}[Motor Group])+Count({$<[Motor Group]={TM1},[WAG]={WAG1}>}[Motor Group])+count({$<[Motor Group]={TM2},[WAG]={WAG1}>}[Motor Group])

On top of this calculation i need to apply set analysis = {<[motor_type]={AC},[Motor Group]={TM1,TM2},[WAG]={WAG1,WAG2}>}

Waiting for your replies.

Regards,

Praveen Prabhu

Re: need quick help with set analysis

shiveshsingh — Mon, 19 Nov 2018 07:33:34 GMT

You can store this expression in a variable and then use set analysis.

or May be this can help..

sum({<[motor_type]={AC},[Motor Group]={TM1,TM2},[WAG]={WAG1,WAG2}>}
count(proficy_ptn)
/
Count({$<[Motor Group]={TM1},[WAG]={WAG2}>}[Motor Group])+count({$<[Motor Group]={TM2},[WAG]={WAG2}>}[Motor Group])+Count({$<[Motor Group]={TM1},[WAG]={WAG1}>}[Motor Group])+count({$<[Motor Group]={TM2},[WAG]={WAG1}>}[Motor Group]))

Re: need quick help with set analysis

praveensp — Mon, 19 Nov 2018 07:42:26 GMT

Nope, this solution is not working.. Any other solution?

Re: need quick help with set analysis

praveensp — Mon, 19 Nov 2018 07:46:16 GMT

Says nested aggregation not allowed.

Re: need quick help with set analysis

shiveshsingh — Mon, 19 Nov 2018 07:56:09 GMT

My bad, it will not work ..
Try storing the data in variable and then use set analysis...

Re: need quick help with set analysis

praveensp — Mon, 19 Nov 2018 08:17:32 GMT

What aggregation shall i use after storing it in variable?

Re: need quick help with set analysis

achates2008 — Mon, 19 Nov 2018 08:40:57 GMT

Try this, a little bit simplified. Not sure if understand your requirement correctly.

besides, those values are not number, so should use single quote.

=count({$<[Motor Group]={'TM1','TM2'},[WAG]={'WAG1','WAG2'}>}proficy_ptn) /
count({$<[Motor Group]={'TM1','TM2'},[WAG]={'WAG1','WAG2'}>}[Motor Group])

Re: need quick help with set analysis

shiveshsingh — Mon, 19 Nov 2018 08:42:25 GMT

The same expression that you have shared before.

Re: need quick help with set analysis

sunny_talwar — Mon, 19 Nov 2018 12:40:44 GMT

May be this

=Count({<[motor_type] = {AC}, [Motor Group] = {TM1, TM2}, [WAG] = {WAG1, WAG2}>}proficy_ptn)/
(Count({$<[motor_type] = {AC}, [Motor Group] = {TM1, TM2}, [WAG] = {WAG1, WAG2}>}[Motor Group])+
Count({$<[motor_type] = {AC}, [Motor Group] = {TM1, TM2}, [WAG] = {WAG1, WAG2}>}[Motor Group])+
Count({$<[motor_type] = {AC}, [Motor Group] = {TM1, TM2}, [WAG] = {WAG1, WAG2}>}[Motor Group])+
Count({$<[motor_type] = {AC}, [Motor Group] = {TM1, TM2}, [WAG] = {WAG1, WAG2}>}[Motor Group]))

Actually, do you mind putting in words as to what exactly are you trying to do here?