exclude values in set analysis

[sanjujeeboy]

Hi All,

I have attached excel file fro my requirement.

My requirement is , for Unit 'NF' it should always Exclude Type 'COL' and rest of values it should consider.

 I am using following expression in set analysis [sum({<Unit={'NF'},Type-={'COL'}>}Amount)] but somehow its not working.

its not taking values where type is not mentioned. how to achieve this output

output should be for FR,Amount - 659

                                            US Amount  793

Country	Unit	Type	Amount
FR	NF		160
FR	NF		110
FR	NF	COL	138
FR	NF		105
FR	NF		175
FR	NF	Video	109
US	NF	COL	254
US	NF		148
US	NF		160
US	NF		110
US	NF		160
US	NF		110
US	NF	COL	138
US	NF		105

[Lisa_P]

It is because your other values are null in the Type field.  You can't select null values, so you also can't see them in set analysis.

If you load them as something other than null, this set analysis will work.

eg

LOAD
  [Country],
  [Unit],
  If(IsNull([Type]),'None', Type) as Type,
  [Amount]
FROM [lib://Community/COL.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Kushal_Chawda]

try below expression

sum({$*1-$<Unit={'NF'},Type={'COL'}>}Amount)

[sanjujeeboy]

Hi Kush,

This solution is working fine

can you explain the logic used? i mean what is $*1-$ doing?

Thanks

[Kushal_Chawda]

Well I will break down is two separate part 

$ * (1-$)

 First $ considers the current selections into the account while second part evaluates the set expression and gives results that has not matched the set analysis condition. So entire part works as a combination of current selections with excluding set 

[Kushal_Chawda · Accepted solution]

Just to note that If you have some other unit apart from 'NF' where you have Type value as 'COL' it will not work as expected. In that case you need to use below so for safer side use below expression. Further if you add more modifier you need to include that in aggr

sum({<Type-={'COL'}>}aggr(sum({$*1-$<Unit={'NF'},Type={'COL'}>}Amount),Unit,Type))

[sanjujeeboy]

Thanks Kush. Really Appreciate!!!