Question on Set Analysis

[#1]

I have a Straight Table chart with dimension as - TEAM_1_NM and an expression as -

=sum({$<TEAM_LEVEL_1_ID={1183693,1666316}, TIER={TEAM_1_NM}>}AM_NET_CVA_ASGN_DELTA*YTDFlag)

There are about 15 rows on this chart. For each row, I want to compare the team id to 2 specific teams and also want to match the team name with another field TIER

I am having trouble with defining the 2nd condition - TIER={TEAM_1_NM}

Can someone suggest a way to fix this please?

[martin59]

Hi,

Try :

TIER={"$(=Only(TEAM_1_NM))"}

[#3]

Martin, thanks for quick reply.

I tried with your solution, but no luck... each row shows 0

the modified expression looks as below -

=sum({$<TEAM_LEVEL_1_ID={1183693,1666316}, TIER={"$(=Only(TEAM_1_NM))"}>}AM_NET_CVA_ASGN_DELTA*YTDFlag)

[johnw]

I don't think you're going to get what you want with set analysis. A set is only built once for the entire chart, not once per row of the chart. Therefore, when referring to TEAM_1_NM in the set, you are referring simultaneously to all possible values of TEAM_1_NM. If you're wanting to match it for each row in the chart, that isn't something set analysis can do, so I think you'll need to use an IF. And as a side note, I think you can apply your YTD flag more efficiently in the set than as a multiplier.

So perhaps this would work instead?

sum({<TEAM_LEVEL_1_ID={1183693,1666316}, YTDFlag={1}>} if(TIER=TEAM_1_NM, AM_NET_CVA_ASGN_DELTA))

[#5]

John,
Thanks for the reply.. Your solution takes one step closer to what I am looking for.. the requirement is kindof complex...

Now the straight table has one dimension as - TIER and 3 expressions as below -

=sum({$<TEAM_LEVEL_1_ID={1183693,1666316}>}AM_NET_CVA_ASGN_DELTA*YTDFlag) //hidden

=sum({<TEAM_LEVEL_1_ID={1456936,1456946,1496168,1633418,1732884,1045468,1413639,
1456935,1204825,1456297,1633417,1717014},YTDFlag={1}>}if(TIER=TEAM_LEVEL_1_NM, AM_NET_CVA_ASGN_DELTA)) //hidden

= Column(1) + Column(2)

What I want to do is - for each row (tier), get the CV amount from 2 teams - 1183693,1666316. This is first column, gives correct result.

Now I also want to get the CV amount from the team where team name matches with the tier name. This is the 2nd column. But now the problem is amount calculated is crosssection between the Team and Tier. Rather I want to ignore the tier completely in this calculation and just want to get the sum of CV for the corresponding team. Since tier itself is the dimension, I am not sure how I can force the expression to ignore it?!

Thanks again for your help... would appreciate if you can suggest some workaround!