ignore selection in set analysis into 'Pick(mach(ValueList'

[#1]

Hi all,

I would like to ignore selections in expressions  that I used in table.

The expression is the following:

=Pick(Match(ValueList('Negotiated','APV','GRN')
,
'Negotiated','APV','GRN'),

Sum ({$<BFTYP= {'ACTUAL'},COMMODITY_SHORT_DESC=, MonthNum={1}>}$(s_ZZPG_FINAL))
,
Sum ({$<BFTYP= {'ACTUAL'},COMMODITY_SHORT_DESC=, MonthNum={1}>}$(s_NETWR))
,
Sum ({$<BFTYP= {'ACTUAL'},COMMODITY_SHORT_DESC=, MonthNum={1}>}$(s_WRBTR))

)

I want that COMMODITY_SHORT_DESC will be ignored. But it doesn't work.

If I use just the set analysis, without Pick(mach(valuelist,  intsted it works:

Sum ({$<BFTYP= {'ACTUAL'},COMMODITY_SHORT_DESC=, MonthNum={1}>}$(s_ZZPG_FINAL))

Can you help me?

Thank you,

Silvia

[marcus_sommer]

Please share a small example.

- Marcus

[#3]

Hi Marcus,

I've attached an example.

if you click on one Commodity, one table works and the other one doesn't works.

Thank you

Silvia

[marcus_sommer]

The behaviour of the application is odd and I would make a backup of them and then create a new one from the scratch (not copy + paste of the objects). And in this new app I wouldn't use any trigger (it's quite the same like: Macros are Bad) and if then only there where it's really mandatory and this implementing as the last step of the development.

Beside them I suggest to consider a change to the data-model. Your fact-table looked like a big cross-table and I'm not sure if it's a good way to use these calculated dimensions. I think it would be better to solve this within the script. Maybe this is a starting point for you: The Crosstable Load.

- Marcus

[pasi_lehtinen]

I had the same requirement. I know this is an old post but here is my solution in case someone has same issues in the future:

if(valuelist('opt1', 'opt2', 'opt3')='opt1', 
  //expression for opt1
,0)
+
if(only({<Field=>}valuelist('opt1', 'opt2', 'opt3'))= 'opt2',
  //expression for opt2
,0)
+
if(only({<Field_xyz=>}valuelist('opt1', 'opt2', 'opt3'))= 'opt3',
  //expression for opt3
,0)

In the example I have 3 different options in the valuelist. Unfortunately this solution is not dynamic enough to also utilize pick + match combination. Nevertheless, I hard coded each case separately, this way I am able to ignore different selections in different cases. This example does not ignore any selections for Opt1, but ignores different fields in Opt2 & Opt3.