This works! Many thanks, I would have never got the aggr in the right
David I Simpson
Business Intelligence and Data Director
West Yorkshire HX3 7PA
Tel: +44 (0) 1274 696063
Fax: +44 (0) 1274 696148
Mobile: +44 (0) 7803 246063
Interface Europe Ltd, Registered in England No. 309779
Registered Office Shelf Mills, Halifax HX3 7PA
Re: Total formula not giving the result I want
created by swuehl in Development (QlikView Desktop) - View the full
Try using advanced aggregation:
Item Extended Price]))
Reply to this message by replying to this email -or- go to the message on
Start a new discussion in Development (QlikView Desktop) by email or at
© 1993-2011 QlikTech International AB Copyright & Trademarks |
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
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))
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:
10 344 Chart will consider only one row, hence it will give correct total
But, Text Box will consider all the three rows in total.
Hope this will help you.