Selections in Set Analysis

[#1]

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.

[tresesco]

Try using GetFieldSelections(), like:

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

[#3 · Accepted solution]

Hi,

Replace the if by,

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

Regards,

Prabhu

[#4]

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

[amit_saini]

Hi Royale,

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

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

Thanks,

AS

[#6]

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