cancel
Showing results for
Did you mean:
Not applicable

## text box calculation

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

1 Solution

Accepted Solutions
MVP

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

3 Replies
MVP

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

MVP

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

Specialist

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

Community Browser