Question on set analysis

[#1]

Hi

I have been trying to resolve this but cant get it working... appreciate if someone can help....

Please look at the attached file.. when I click on Americas and select Yes for Exclude primary revenue, the chart should still show revenue across all regions but it should exclude primary revenue.. all primary revenue is attributed towards Primary business in this example. But that may not be the case all the time. So cannot write the logic using L3_NM

Primary revenue flag is determined based on SEC_REV_FLAG that is part of every revenue record.

Problem is if I select a particular EQ_L3_NM, it excludes primary revenue for that selected region but not from all other regions... I am using below expression -

=if(only(EXCLUDE_PRIMARY_REVENUE_FLAG)='Yes',

sum({<EQ_L3_NM=,EQ_L2_NM=>} F_CV_YTD*SEC_REV_FLAG)/1000,

sum({<EQ_L3_NM=,EQ_L2_NM=>} F_CV_YTD)/1000

)

thanks in advance for any suggestions..

[#2]

try with if(only(total ...

[#3]

sorry.. I didnt quite understand.. .can you elaborate more please...

[#4 · Accepted solution]

use this expression:

=if(only(total EXCLUDE_PRIMARY_REVENUE_FLAG)='Yes',

sum({<EQ_L3_NM=,EQ_L2_NM=>} F_CV_YTD*SEC_REV_FLAG)/1000,

sum({<EQ_L3_NM=,EQ_L2_NM=>} F_CV_YTD)/1000

)

[jolivares]

Try this expressions:

YTD -> sum({<EQ_L3_NM=,EQ_L2_NM=>} if(EXCLUDE_PRIMARY_REVENUE_FLAG='Yes',F_CV_YTD*SEC_REV_FLAG/1000,F_CV_YTD)/1000)

Last YTD ->sum({<EQ_L3_NM=,EQ_L2_NM=>} if(EXCLUDE_PRIMARY_REVENUE_FLAG='Yes',(F_CV_PREV_YTD*SEC_REV_FLAG)/1000,F_CV_PREV_YTD/1000))

FY -> sum({<EQ_L3_NM=,EQ_L2_NM=>} if(EXCLUDE_PRIMARY_REVENUE_FLAG='Yes',(F_CV_PREV_YEAR*SEC_REV_FLAG)/1000,F_CV_PREV_YEAR/1000))

[#6]

thank you both...