4 Replies Latest reply on Sep 25, 2018 9:22 PM by Mahfooj Khan

# How to create a set?

Hello,

i would like a dropdown filter called "Ice and Water included?" with 2 values "Yes" and "No".

When i select "Yes", i want the total sales for IDs 1 to 10.

When i select "No, i only want sales for IDs 1-4 and 7-10.

Can someone please help? I have a feeling it has something to do with creating sets? I'm not too familiar...

thank you!

 ID Description Sales 1 Apple 10 2 Banana 20 3 Cookies 30 4 Melon 40 5 Ice 5 6 Water 10 7 Grass 25 8 Juice 15 9 Tree 10 10 Cat 5
• ###### 1. Re: How to create a set?

Hi Anky,

In attached workbook you'll find the solution. I have created a set of 5 & 6 and added this set to the filter card. Then, I've set it as In/Out and showed it as filter. Hope this helps.

Thanks,

Shiva.

• ###### 2. Re: How to create a set?

Hi Anky,

Let me know if that helps.

~Tushar

• ###### 3. Re: How to create a set?

Hi Anky,

You can try below logic:

1. Create a parameter :

2. Create a variable :

Flag =  if [Ice and Water included?]="Yes" then TRUE  ELSEIF [Ice and Water included?]="No" AND([Description]="Ice" or [Description]="Water") THEN FALSE  ELSE TRUE END

Use this flag in filter shelf and select "True" to get the desired result.

• ###### 4. Re: How to create a set?

Hi,

Find my approach below,

Create an integer parameter like this,

then link this parameter in a calculated field to get the total sales

CASE [Ice & Water Included]

WHEN 0 THEN {SUM([Sales])}

WHEN 1 THEN {SUM(IIF([ID]!=5 AND [ID]!=6,[Sales],NULL))}

END

Note: Change the LOD{} as per your requirement

Drag the field and show the parameter and check.

Hope this will help.

Mahfooj