topic Re: calculation using nested sum in QlikView

calculation using nested sum

jood_ahmad — Tue, 08 Nov 2016 15:14:16 GMT

Dears,

Can you advice on this calculation , what is wrong

=Sum(sum(

Sum(ACT_QTY_CLOSE) * Sum(UNIT_EQUIVALENCE))

Sum((Sum(ACT_QTY_OPEN) *Sum(UNIT_EQUIVALENCE)) + (sum(ITEM_QUANTITY_PUR * ITEM_PURCHASE_EQUIVALENCE) * Sum(UNIT_EQUIVALENCE)))

Sum((sum(ITEM_CONSUMPTION)* Sum(UNIT_EQUIVALENCE)) + Sum(WASTAGES))

* ITEM_PURCHASE_PRICE)

any help please.

ecolomer‌rubenmarin‌

Message was edited by: Ahmad Kastero

Re: calculation using nested sum

marcus_sommer — Tue, 08 Nov 2016 15:20:10 GMT

It's not possible to nest aggregations without an AGGR...‌

- Marcus

Re: calculation using nested sum

jood_ahmad — Tue, 08 Nov 2016 15:25:53 GMT

Hi Marcus,

i try to do it wit Aggr but it is not work ,

i try with the main field that i need to do the calculation on it (%Cons_Item_Key) .

is there is another way or only we can do it by using AGGR.

Re: calculation using nested sum

rubenmarin — Tue, 08 Nov 2016 15:32:19 GMT

Hi Ahmad, maybe using RangeSum:

RangeSum(

RangeSum(Sum(ACT_QTY_CLOSE) * Sum(UNIT_EQUIVALENCE))),

RangeSum(Sum(ACT_QTY_OPEN) *Sum(UNIT_EQUIVALENCE),

sum(ITEM_QUANTITY_PUR * ITEM_PURCHASE_EQUIVALENCE) * Sum(UNIT_EQUIVALENCE))

-RangeSum(sum(ITEM_CONSUMPTION)* Sum(UNIT_EQUIVALENCE),

Sum(WASTAGES))

) * ITEM_PURCHASE_PRICE)

Not tested, maybe there is a typo... and I never used RangeSum this way, so I'm not sure if this can work.

Re: calculation using nested sum

jood_ahmad — Tue, 08 Nov 2016 15:53:05 GMT

Thank you Ruben, but unfortunately not working .

Re: calculation using nested sum

marcus_sommer — Tue, 08 Nov 2016 15:53:52 GMT

If you could logically replace the sum() around the sum(Fields) with a + than you could go with the approach from Ruben otherwise you will need the aggr() with the proper dimensions to those parts where you couldn't replace them.

- Marcus

Re: calculation using nested sum

marcus_sommer — Tue, 08 Nov 2016 15:56:42 GMT

Do it step by step and give each part a single expression and if they work like expected put them together and check again.

- Marcus

Re: calculation using nested sum

rubenmarin — Tue, 08 Nov 2016 16:22:35 GMT

I did it more simpler and worked but, as I said, I'm not sure if that complexity can work (I don't see why not).

And also it was written directly in the post, so typo's may appear, rigth now I saw a lack of a comma:

Sum(UNIT_EQUIVALENCE)), //This comma was forgotten in my previous post

Re: calculation using nested sum

jood_ahmad — Wed, 09 Nov 2016 11:50:56 GMT

Dears,

i add a sample for my target bar chart and my reference value in table ( Recipe Consumption) if you see in this table the last column is the VALUED DIFFERENCE this column result of all that nested sum but when i try to do it in bar chart i can't.

i need to see for each item the total of VALUED DIFFERENCE in bar chart even (Positive or Negative value). this is my target.

Re: calculation using nested sum

rubenmarin — Wed, 09 Nov 2016 12:06:23 GMT

Hi Ahmad, the expression 'Theo.Cons.Recipe Qty' is a conditional expression, when the condition is false the expression is not calculated, so every other expression that uses this expression can't be calculated.

You can transform the null in 0 using Alt(): Alt([Theo.Cons.Recipe Qty],0), but the deference% will also return null (it will be a division by zero).

If what you want is to hide this column but still use the values use the conditional show in the presentation tab instead of the conditional expression of the expression tab.

And the VALUE difference column can't be calculated that way because it has many different values for ITEM_PURCHASE_PRICE, it should be only one value (using an expressiont hat returns that value) or using aggr() so it uses different ITEM_PURCHASE_PRICE for each Aggr() dimension value... but I think this wouldn't be fit the intention of the expression so you need to kwon what to do with all the different ITEM_PURCHASE_PRICE... wich value has to be used in VALUE DIFFERENCE?

Re: calculation using nested sum

rubenmarin — Wed, 09 Nov 2016 12:54:35 GMT

BTW, once the table shows the desired result you can easily transform to a bar chart, just check the 'invisible' option on the expressions tab for each expression except the one you want to show.

Re: calculation using nested sum

jood_ahmad — Wed, 09 Nov 2016 13:16:40 GMT

HI Ruben,

in the first sheet Recipe consumption all the calculation to convert all my unit from (Stock Room unit in DB) to see it in recipe unit and i validate all that with my DB .

the value that i use it to get the VALUED DIFFERENCE is all the column before the VALUED DIFFERENCE expression (

END.THE.QTY=([BEGINNING QTY] + [Purchase QTY ])-( [Theo.Cons.Recipe Qty] +[LOSSES WASTES] )

[BEGINNING QTY] =Sum(ACT_QTY_OPEN) *Sum(UNIT_EQUIVALENCE)

[Purchase QTY ]=sum(ITEM_QUANTITY_PUR * ITEM_PURCHASE_EQUIVALENCE) * Sum(UNIT_EQUIVALENCE)

[Theo.Cons.Recipe Qty]=$(vTheoretCons_Recipe)

==< sum(ITEM_CONSUMPTION)* Sum(UNIT_EQUIVALENCE) >==

[LOSSES WASTES] =Sum(WASTAGES)

[END.ACT.QTY] = Sum(ACT_QTY_CLOSE) * Sum(UNIT_EQUIVALENCE)

[DEFFERENCE] = [END.ACT.QTY] - [END.THE.QTY]

)

all the above is to get the DEFFERENCE QTY and multiplied it with the Price

VALUED DIFFERENCE=[DEFFERENCE] * ITEM_PURCHASE_PRICE .

in this table is working fine but when i try to build the bar chart report only for VALUED DIFFERENCE over the Item as Dimension i need to calculate again all the above in one expression and multiply it with the Price to get the same number as the table VALUED DIFFERENCE number ; but this is not working fine with me .

and the 'Theo.Cons.Recipe Qty' is a conditional expression, i use it to show only with the group in the Item level not in the branch level.

Re: calculation using nested sum

jood_ahmad — Wed, 09 Nov 2016 13:31:12 GMT

i do this and this is work in table but when i try to do it in bar chart in one expression it is not working fine.

Re: calculation using nested sum

jood_ahmad — Wed, 09 Nov 2016 13:33:35 GMT

i try to do fast change to chart and it's changed to bar chart but it shown all the other values and i check the 'invisible' option and i kept the VALUE DIFFERENCE nothing appear in the chart.

Re: calculation using nested sum

rubenmarin — Wed, 09 Nov 2016 13:40:33 GMT

Hi Ahmad, I unchecked the conditional expression of [Theo.Cons.Recipe Qty] and changed the VALUE DIFFERENCE expression to "=[DEFFERENCE] * Avg(ITEM_PURCHASE_PRICE)". This is to allow the expression to return a value, if the table doesn't shows any value in this column it will not show any value in chart, so the first thing is work with the table so it returns a value for that expression

Checked invisible in all expressions but the VALUE DIFFERENCE and changed to bar chart. It shows the values of the table.

Re: calculation using nested sum

jood_ahmad — Wed, 09 Nov 2016 14:26:43 GMT

Thank you Ruben it is look working on the item level but when i put it on the branch level not calculated correctly i will investigate this.

i have 2 or 3 question :

1. what is the way to use like this nested calculation in one expression?

2.when i select like a small item that have small amount on in the negative value the bar size not changed, i thing because it take the same value from the table and not interact with the value selected to increasing bar size ; is there is any way to solve this.

3. you use the AVG to get the value ; but as i know this to get the Average of any calculated number ; how it is work here.

and really thanks for help me on this .

Re: calculation using nested sum

rubenmarin — Wed, 09 Nov 2016 14:45:54 GMT

Hi Ahmad, usually the way to go is what Marcus said: using Aggr(). But your whole expression seems complicated to work with Aggr() (and I'm not uderstanding the logic behind... I lack of free time to deeply check .

About the current table, looking it by item the numbers seems more reasonable, you should review all the expressions. In example for the BEGINNING QTY it should be:

Sum(Aggr(Sum(ACT_QTY_OPEN) *Sum(UNIT_EQUIVALENCE), PURCHASE_ITEM_DESCRIPTION))

// This will do an independent operation for each item in the branch and then sums, and also works when you see it by items

To check incosistent values, add totals to all columns, select a branch and check if the values shown when you group by branch is the same value of the total when you see it by item.

About the questions:

1. using Aggr() or RangeSum()

2. Have you checked the axis values? maybe the bar looks the same but axis values have changed

3. I used Avg just to obtain a value, it wouldn't be to correct way to calculate the expression... sorry I don't have enough free time to try to understand the intended logic behind and how to obtain the correct values but as general comment you can't use the same expressions when you look by item than by branch, the general fix will be similar than the one I posted about BEGINNING QTY: using aggr to calculate each item separatedly, then sum the values returned by Aggr()

Re: calculation using nested sum

jood_ahmad — Thu, 10 Nov 2016 13:32:23 GMT

Ruben my friend thank you very much for your effort, i find way to do it in one expression .

thank you very much and i will post it soon.

Re: calculation using nested sum

jood_ahmad — Mon, 14 Nov 2016 07:18:02 GMT

finaly i put in one expression:

=(Sum(ACT_QTY_CLOSE * UNIT_EQUIVALENCE) -

((Sum(ACT_QTY_OPEN * UNIT_EQUIVALENCE) + sum(ITEM_QUANTITY_PUR * ITEM_PURCHASE_EQUIVALENCE * UNIT_EQUIVALENCE)) -

(sum(ITEM_CONSUMPTION * UNIT_EQUIVALENCE) )- Sum(WASTAGES)))* ITEM_PURCHASE_PRICE

Re: calculation using nested sum

rubenmarin — Mon, 14 Nov 2016 10:11:16 GMT

OK, I didn't tested but I think this wouldn't work when the first dimension is branch.

If it doesn't works for branch, as a quick test you can try:

Sum(Aggr(

Your_actual_expression

, ItemFieldName))

ItemFieldName: The field that identifies each different item