Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

davidsimpson
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.

67855303210890
101500015000
18210000210000
3220000002000000
51100000100000
52155100155100
654400044000
72200000200000
764171841718
782781227812
8625602560
894500045000
906100061000
918400028000
923000030000
944000020000
1066750067500
107208000100000
1081200012000
11823001200
1195000050000

Any ideas? Please.

Thanks in advance.

1 Solution

Accepted Solutions
MVP
MVP

Total formula not giving the result I want

Try using advanced aggregation:

=sum(aggr(

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

, YOURTABLEDIMENSIONFIELD))

12 Replies
MVP
MVP

Total formula not giving the result I want

Try using advanced aggregation:

=sum(aggr(

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

, YOURTABLEDIMENSIONFIELD))

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

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

v_iyyappan
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.

Hope this will help you.

davidsimpson
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.

davidsimpson
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

Business Intelligence and Data Director

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

Try using advanced aggregation:

=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

© 1993-2011 QlikTech International AB Copyright & Trademarks |

Privacy | Terms of Use | Software EULA

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

attachments from your system. http://www.interfaceglobal.com

davidsimpson
New Contributor III

Total formula not giving the result I want

Many thanks, this is just what I needed

davidsimpson
New Contributor III

Re: Total formula not giving the result I want

Many thanks, this is just what I needed

Community Browser