topic Question on set analysis in QlikView

Question on set analysis

Fri, 02 Dec 2011 20:41:55 GMT

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..

Question on set analysis

Fri, 02 Dec 2011 20:55:52 GMT

try with if(only(total ...

Question on set analysis

Fri, 02 Dec 2011 20:58:43 GMT

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

Question on set analysis

Fri, 02 Dec 2011 21:01:16 GMT

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

)

Question on set analysis

jolivares — Fri, 02 Dec 2011 21:14:42 GMT

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))

Question on set analysis

Fri, 02 Dec 2011 21:37:45 GMT

thank you both...