topic Re: Set analysis issue in QlikView

Set analysis issue

Anonymous — Wed, 11 Dec 2013 11:10:10 GMT

I am trying to use set analysis to work out sales as a % of total sales for a selection (region) compared to total sales (country) within a selected year by age group.
With age group as my dimension, I have used the following expresion to calculate sales for the selection -

SUM(sales)/SUM(TOTAL(sales))

I have been trying to use set analysis to compare this with sales as a % of total sales for the country. I have used the following expression -

SUM({1}sales)/SUM({1}TOTAL sales)

Unfortnunately, the country % isnt showing the correct value. I think it might be excluding the selction 'Year' and working out sales as a % of total sales for all years.

I would be grateful for any advice.

Thanks

Greg

Re: Set analysis issue

manideep78 — Wed, 11 Dec 2013 11:19:44 GMT

Can you please post the sample application and data?

Re: Set analysis issue

Anonymous — Wed, 11 Dec 2013 11:51:21 GMT

Sorry, I don't know how to post the sample application and data.

I have copied the straight table below -

I haven't selected a region here. All I have selected is the year. Therefore, the selection sales is the Scotland total. However, I think the expression for Scotland sales is ignoring my selection and showing the sales for all years.

Re: Set analysis issue

manideep78 — Wed, 11 Dec 2013 12:03:58 GMT

SUM({1}sales)/SUM({1}TOTAL sales)

This expression will definitely ignore your selection. Set Identifier 1 ignores all the selection and gives result for entire data. Click on Use advance editor and attach your files

Re: Set analysis issue

Anonymous — Wed, 11 Dec 2013 12:40:02 GMT

have you use Year= in your set analysis in scotland sales column if yes then remove it

and can you tell me code that you used in your last two column of straight table?

anant

Re: Set analysis issue

Anonymous — Wed, 11 Dec 2013 12:41:09 GMT

have you use Year= in your set analysis in scotland sales column if yes then remove it

and can you tell me code that you used in your last two column of straight table?

anant

Re: Set analysis issue

Wed, 11 Dec 2013 12:43:54 GMT

Try to use the $ instead of the 1 in the set analysis. Then you should take the selected values into account...

Re: Set analysis issue

annafuksa1 — Wed, 11 Dec 2013 12:51:17 GMT

Please see attachment

Re: Set analysis issue

Anonymous — Wed, 11 Dec 2013 13:02:15 GMT

Thankyou, thankyou, thankyou Anna. This worked beautifully.

Re: Set analysis issue

Anonymous — Wed, 11 Dec 2013 13:23:37 GMT

Hi Anna.

Sorry to bother you again. Do you know if I can ignore other fields (in addition to region) in the following expresion. I would like it to ignore when someone selects a college within a region too.

Sum( {$<region = >} Sales) I would like it to ignore when someone selects a college within a region too.

Re: Set analysis issue

annafuksa1 — Wed, 11 Dec 2013 13:38:00 GMT

yes, you can do the same it gonna look

Sum( {$<region = , college= >} Sales)

expresion 'field =' make that Qlikview will ignore current selection

or if you have for example Collage: A and B

you can use expression

Sum( {$<region = , college= {'A', 'B'}>} Sales)

it gonna work the same just first version is just easier , Second is good when you want to choose only one collage

Re: Set analysis issue

Anonymous — Wed, 11 Dec 2013 13:41:54 GMT

Thanks you very much Anna.