text box calculation

[#1]

Hi Experts,

i have to do a calculation in text box.

my expression is

(SUM({<MonthYear=, Date={"$(vCurrWK)"}>}SPAMT)-SUM({<MonthYear=, Date={"$(vCurrWK)"}>}GMCPAMT))/

SUM({<MonthYear=, Date={"$(vCurrWK)"}>}MGN_CUR_TGT)

this expression is calculated in %

Now i need to count the branches which has the above expression >100% and <110% in a text box.

my branch is set with variable vdim1.

How can i do this...

pls help

[sunny_talwar]

The expression you mentioned above was earlier used in a text box or a chart? and was it giving you the results you expected?

[sunny_talwar · Accepted solution]

Try this:

Count(If(Aggr((SUM({<MonthYear=, Date={"$(vCurrWK)"}>}SPAMT)-SUM({<MonthYear=, Date={"$(vCurrWK)"}>}GMCPAMT))/ SUM({<MonthYear=, Date={"$(vCurrWK)"}>}MGN_CUR_TGT), Branch) > 1 and Aggr((SUM({<MonthYear=, Date={"$(vCurrWK)"}>}SPAMT)-SUM({<MonthYear=, Date={"$(vCurrWK)"}>}GMCPAMT))/ SUM({<MonthYear=, Date={"$(vCurrWK)"}>}MGN_CUR_TGT), Branch) < 1.1, Branch))

[maleksafa]

you need to use the aggr function.

supposing branches dimension is branch:

count(if(aggr(

(SUM({<MonthYear=, Date={"$(vCurrWK)"}>}SPAMT)-SUM({<MonthYear=, Date={"$(vCurrWK)"}>}GMCPAMT))/

SUM({<MonthYear=, Date={"$(vCurrWK)"}>}MGN_CUR_TGT),branch) > 1, branch)

count(if(aggr(

(SUM({<MonthYear=, Date={"$(vCurrWK)"}>}SPAMT)-SUM({<MonthYear=, Date={"$(vCurrWK)"}>}GMCPAMT))/

SUM({<MonthYear=, Date={"$(vCurrWK)"}>}MGN_CUR_TGT),branch) <= 1, branch)

so here what i did, is to run this expression by branch (using the aggr function) and then if the result is greater than 1 i will count the branch. Same applied when the result is less than or equal to 1