# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

New Contributor III

## Total formula not giving the result I want

I am having trouble with totals. I have managed to get the correct total in a stragit table by using the Sum of Rows. However I want to repeat this total in a text box but I can only get the total that the straight table displays when expression total is selected.

I guess the sum of rows is doing something that I am missing from my formula in my text box.

=if(sum([Local Line Total Amount]=0),sum([Local Budget Amount]),sum([Local Item Extended Price]))  this keeps giving me 6785530 but I know it should be 3210890

This is the table with totals at the top. Clearly the first column is totalled incorrectly and the second is correct but my formula gives me the first total.

 6785530 3210890 10 15000 15000 18 210000 210000 32 2000000 2000000 51 100000 100000 52 155100 155100 65 44000 44000 72 200000 200000 76 41718 41718 78 27812 27812 86 2560 2560 89 45000 45000 90 61000 61000 91 84000 28000 92 30000 30000 94 40000 20000 106 67500 67500 107 208000 100000 108 12000 12000 118 2300 1200 119 50000 50000

Tags (7)
1 Solution

Accepted Solutions
MVP

## Total formula not giving the result I want

=sum(aggr(

if(sum([Local Line Total Amount]=0),sum([Local Budget Amount]),sum([Local Item Extended Price]))

, YOURTABLEDIMENSIONFIELD))

12 Replies
MVP

## Total formula not giving the result I want

=sum(aggr(

if(sum([Local Line Total Amount]=0),sum([Local Budget Amount]),sum([Local Item Extended Price]))

, YOURTABLEDIMENSIONFIELD))

MVP

## Total formula not giving the result I want

Hi,

Change the option in the Expression tab of the chart properties under the Total mode as Sum of rows.

Celambarasan

Honored Contributor II

## Total formula not giving the result I want

hi

the diffrence i guess is because when you use some of rows

what happen is that you check the

if(sum([Local Line Total Amount]=0) for every row so some get the first expression and some  the second

in the  expression total and the text  box the if clause is evaluated on all the  data once so i guess

it calculate sum(local Budget Amount) on all the data

you can change the formula to :

let say your dimension is Department_ID

so you can use

sum(aggr(if(sum([Local Line Total Amount]=0),sum([Local Budget Amount]),sum([Local Item Extended Price])),Department_ID))

Valued Contributor

## Re: Total formula not giving the result I want

Hi,

See the attached file, Hope its helpful for u.

Regards,

Iyyappan.

Not applicable

## Total formula not giving the result I want

Hey David,

I think, something wrong is there with your Data Model, Just check out the frequency of the Values in a list box.

It happens when you have a repeating value against a Dimension. Since Charts dont consider these repeating values due to improper linking in total,hence it will come accurate there.

But in text box it will consider all values including the repeating ones.

Case of repeating values , scenario would be like this:

Dimension   Expression

10               344       Chart will consider only one row, hence it will give correct total

10               344

10               344

But, Text Box will consider all the three rows in total.

New Contributor III

## Total formula not giving the result I want

Hi Swuehl, this did exactly what I wanted! Many thanks, I thought it was maybe aggr but would have never got the syntax right.

New Contributor III

## Re: Total formula not giving the result I want

This works! Many thanks, I would have never got the aggr in the right

place.

David I Simpson

InterfaceFLOR

Shelf Mills

Halifax

West Yorkshire HX3 7PA

United Kingdom

Tel: +44 (0) 1274 696063

Fax: +44 (0) 1274 696148

Mobile: +44 (0) 7803 246063

http://www.interfaceflor.eu

http://www.interfaceglobal.com

Interface Europe Ltd, Registered in England No. 309779

Registered Office Shelf Mills, Halifax HX3 7PA

From:

swuehl <qliktech@sgaur.hosted.jivesoftware.com>

To:

DavidSimpson <dave.simpson@interfaceflor.eu>

Date:

14/02/2012 12:05

Subject:

- Re: Total formula not giving the result

I want

QlikCommunity

Re: Total formula not giving the result I want

created by swuehl in Development (QlikView Desktop) - View the full

discussion

=sum(aggr(

if(sum(=0),sum(),sum([Local

Item Extended Price]))

, YOURTABLEDIMENSIONFIELD))

Reply to this message by replying to this email -or- go to the message on

QlikCommunity

Start a new discussion in Development (QlikView Desktop) by email or at

QlikCommunity

This message and any attachments may contain information that is privileged

and/or confidential and is the property of Interface. It is intended solely

for the person to whom it is addressed. If you are not the intended

recipient, you are hereby notified that you are not authorised to read,

print, retain, copy, disseminate, distribute, or use this message and any

attachments or any part thereof. If you have received this message in

error, please notify the sender immediately and delete the message and any