topic Re: text box calculation in QlikView

text box calculation

Fri, 12 Jun 2015 11:00:40 GMT

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

Re: text box calculation

sunny_talwar — Fri, 12 Jun 2015 11:08:58 GMT

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

Re: text box calculation

sunny_talwar — Fri, 12 Jun 2015 11:13:58 GMT

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

Re: text box calculation

maleksafa — Fri, 12 Jun 2015 11:17:42 GMT

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