Summation error

[stephenhasson]

Hello,

I have a calculated field in a grid that works fine, taking a value X and multiplying it by the value Y to give Z.

X is pulled from a part's record representing an amount of explosive per KG, Y is the qty (in KG) of a part in stock. Z = the total explosive amount for the part.

On a grid showing all the parts, quantities, the warehouse codes etc. the calculation works fine:

=round(NEQPerItemKG*sum(OnhandQty), .00001)

But if I have a similiar grid, but take out the part/qty etc, in order to see totals by warehouse, the calculation doesn't work.

Screen shots attached. Any help appreciated.

Thanks.

[vkish16161]

More explanation needed where the calculation is failing.

What formula are you using and what's the expected output?

[sunny_talwar]

Try this

Sum(Aggr(Round(NEQPerItemKG*sum(OnhandQty), .00001), Code, Warehouse, PartNum, Description, LotNum))

[stephenhasson · Accepted solution]

Thanks both for your replies.

I'd just added a copy of the app when the second reply came in - and this works perfectly.

I really need to get my head round the set analysis...... I think this says 'do this calculation on the whole dataset, irrespective of any selections that have been made on these 5 fields', am I right?

Thank you.

Stephen

[sunny_talwar]

The syntax that I provided isn't called set analysis.... it is just a way to get total from a more granular level dimensions... So within the Aggr() function, it will created a imaginary table and perform all the calculations at that level and then sum them up just like the table you have below in the snapshot. All selections will be honored, so no need to worry on that front

[stephenhasson]

Great, thanks for your help. I appeciate it.

Regards,

Stephen

Extension : 7060

Telephone : +44 1294 487060

[sunny_talwar]

Yup, no problem

Please close this thread if everything is working for you...

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

[stephenhasson]

Great, thanks for your help. I appeciate it.

Regards,

Stephen

Extension : 7060

Telephone : +44 1294 487060