Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead 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
sunny_talwar

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

View solution in original post

3 Replies
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

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