topic Re: Selections in Set Analysis in QlikView

Selections in Set Analysis

Tue, 01 Jul 2014 09:20:08 GMT

Hi,

I'm trying to create some set analysis code which which executes two different sums on different metrics depending on the outcome of an IF statement.

Basically if dimensions Name or Area are selected by the user from their respective List Boxes, I want to get the sum of Sales. Otherwise I want to sum the Sales Forecast.

I have written it in the following way:

if([Name]='*' and [Area]= '*',

sum({<Month={$(=month(max([Mth Yr])))}, Year={$(=max(Year)), [Day Type]={0}>} [Sales Forecast]),

sum({<Month={$(=month(max([Mth Yr])))}, Year={$(=max(Year))}>}[Sales Amount]))

The IF statement does not work - how do I incorporate selections from List Boxes in the script?

Thanks in advance.

Re: Selections in Set Analysis

tresB — Tue, 01 Jul 2014 09:22:59 GMT

Try using GetFieldSelections(), like:

If ( GetFieldSelections(Name)>0 and GetFieldSelections(Area)>0 , ...., ....)

Re: Selections in Set Analysis

Tue, 01 Jul 2014 09:24:29 GMT

Hi,

Replace the if by,

if(GetSelectedCount(Name)>0 And GetSelectedCount(Area) > 0, <Forecast>, <Sales>)

Regards,

Prabhu

Re: Selections in Set Analysis

Tue, 01 Jul 2014 09:29:39 GMT

try this

if(len([Name])>0 or len([Area])>0,

sum({<Month={$(=month(max([Mth Yr])))}, Year={$(=max(Year)), [Day Type]={0}>} [Sales Forecast]),

sum({<Month={$(=month(max([Mth Yr])))}, Year={$(=max(Year))}>}[Sales Amount]))

Thank you

Vardhan

Re: Selections in Set Analysis

amit_saini — Tue, 01 Jul 2014 09:36:28 GMT

Hi Royale,

Try this (Here I'm considering my selection for Plant list)

='Customer Stoppers'&chr(32)&if(GetSelectedCount(Plant)>0,'- '&GetFieldSelections(Plant)&'')

Thanks,

Re: Selections in Set Analysis

Wed, 02 Jul 2014 02:45:32 GMT

Thank you all for your replies, much appreciated, all useful answers. I used the GetSelectedCount solution from Prabhu, thanks!