topic Re: Set analysis problem in QlikView

Set analysis problem

Anonymous — Wed, 16 Jan 2013 12:53:30 GMT

I'm trying to create a chart that only brings in a value for certain varaible, I've created the expression below

Sum({$< ACCAccount ={12170,12340,12350,13910,"2A600","2AB00"}>} GLBal.YTDActual)

This does give me the information but it is split by each ACCAaccount, what I need is one total for all 7 codes

Re: Set analysis problem

swuehl — Wed, 16 Jan 2013 13:11:12 GMT

Try the TOTAL qualifier:

Sum({$< ACCAccount ={12170,12340,12350,13910,"2A600","2AB00"}>} TOTAL GLBal.YTDActual)

Re: Set analysis problem

Anonymous — Wed, 16 Jan 2013 13:19:37 GMT

No that didn't work, seemed to bring through the tatal for all the ACCAaccount not just the 7 I'm interested in.

Re: Set analysis problem

sujeetsingh — Wed, 16 Jan 2013 13:45:44 GMT

Hi,

one thing i noticed is that you are always asking for 7 codes but in set analysis there are only 6 codes

And please come with a complete briefing.

Thanks

Re: Set analysis problem

Anonymous — Wed, 16 Jan 2013 13:49:03 GMT

Sorry only 6 codes, I miss counted

Re: Set analysis problem

swuehl — Wed, 16 Jan 2013 13:57:18 GMT

It should return the total only for the set of values, but it will display this total for all dimension values, not only for the values in your set.

If you are going to use another aggregation in this expression with the same set, e.g. to calculate a percentage, this should overcome the issue:

= Sum({$< ACCAccount ={12170,12340,12350,13910,'2A600','2AB00'}>} GLBal.YTDActual) /

Sum({$< ACCAccount ={12170,12340,12350,13910,'2A600','2AB00'}>} TOTAL GLBal.YTDActual)

Or just define that you only want to see the total for the dimension values in question:

if( match(ACCAcount,'12170','12340','12350','13910','2A600','2AB00'), Sum({$< ACCAccount ={12170,12340,12350,13910,'2A600','2AB00'}>} TOTAL GLBal.YTDActual) )

Re: Set analysis problem

kouroshkarimi — Wed, 16 Jan 2013 13:57:32 GMT

"but it is split by each ACCAaccount"

What is the dimension in the chart?

Re: Set analysis problem

Anonymous — Wed, 16 Jan 2013 14:16:16 GMT

The two dimesions are ACCAaccount and Period

Re: Set analysis problem

kouroshkarimi — Wed, 16 Jan 2013 14:21:58 GMT

If you want one total for all 6 codes, then I would remove the ACCAccount field as a dimension.

Re: Set analysis problem

Anonymous — Wed, 16 Jan 2013 15:09:24 GMT

Thnaks resolved my problem