Including only 1 dimension in expression

Sun, 08 Feb 2015 12:41:31 GMT

Hello All,

I have question on include/exclude dimensions in expression:

My current expression is below, this calculates the aveerage of the gross margin by country in a text box. I want this expression to only update when a country field is selected. There are other fields in the table such as customer, manager, employee etc. but I want this expression to update only for Country selected. Please advise how this can be achieved.

=num(IF(ProjectNumber<>'N/A',SUM({<ProjectNumber=>}
(aggr(sum(if(USD_Net_Net_Amt>0,USD_Net_Net_Amt)),ProjectNumber)-num(aggr(sum(if(USD_STD_Cost>0,USD_STD_Cost)),ProjectNumber)))
/aggr(sum(if(USD_Net_Net_Amt>0,USD_Net_Net_Amt)),ProjectNumber))
/count(distinct IF(aggr(sum({<ProjectNumber=>}if(USD_Net_Net_Amt>0,USD_Net_Net_Amt)),ProjectNumber)<>0 and aggr(sum({<ProjectNumber=>}if(USD_STD_Cost>0,USD_STD_Cost)),ProjectNumber)<>0 ,ProjectNumber))
),'###,#0%')

Thanks!

Re: Including only 1 dimension in expression

Anonymous — Sun, 08 Feb 2015 15:45:00 GMT

Hi,

you can add 1 before set analysis and p() function for Country field: in this way, thanks to 1, no one selection will change expression, but p() function ensures you to change expression respect to selection on Country.

Example: sum({1<ProjectNumber=, Country=p(Country)>} Field)

BR,

Elena

topic Including only 1 dimension in expression in Qlik Learning Discussions

Including only 1 dimension in expression

Re: Including only 1 dimension in expression