topic Total formula not giving the result I want in QlikView

Total formula not giving the result I want

Anonymous — Tue, 14 Feb 2012 12:00:34 GMT

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

Any ideas? Please.

Thanks in advance.

Total formula not giving the result I want

swuehl — Tue, 14 Feb 2012 12:05:12 GMT

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

CELAMBARASAN — Tue, 14 Feb 2012 12:05:22 GMT

Hi,

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

Celambarasan

Total formula not giving the result I want

lironbaram — Tue, 14 Feb 2012 12:10:43 GMT

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

Re: Total formula not giving the result I want

v_iyyappan — Tue, 14 Feb 2012 12:15:17 GMT

Hi,

See the attached file, Hope its helpful for u.

Regards,

Iyyappan.

Total formula not giving the result I want

Tue, 14 Feb 2012 12:19:00 GMT

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

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

Hope this will help you.

Total formula not giving the result I want

Anonymous — Tue, 14 Feb 2012 13:24:04 GMT

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

Re: Total formula not giving the result I want

Anonymous — Tue, 14 Feb 2012 13:26:06 GMT

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

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

Total formula not giving the result I want

Anonymous — Tue, 14 Feb 2012 13:37:13 GMT

Many thanks, this is just what I needed

Re: Total formula not giving the result I want

Anonymous — Tue, 14 Feb 2012 13:37:33 GMT

Many thanks, this is just what I needed

Re: Total formula not giving the result I want

Anonymous — Tue, 14 Feb 2012 13:38:32 GMT

Thanks, this works perfectly.

Re: Total formula not giving the result I want

Anonymous — Tue, 14 Feb 2012 13:42:21 GMT

Thanks for the reply - whilst you may well be correct about the data model the aggr function seems to have fixed my issue so I won't go looking for trouble by re-examing the data model.

Re: Total formula not giving the result I want

Anonymous — Tue, 14 Feb 2012 13:42:39 GMT

Thanks for the reply - whilst you may well be correct about the data model the aggr function seems to have fixed my issue so I won't go looking for trouble by re-examing the data model.