Qlik Sense App Development

New Contributor II

how to calculate the sum of a calculated sum

Hey guys,

I'm searching for help which might be easy for you, but costs days for me to find out a solution...

Original data I have like this in 2 tables.

How Can I calculate / show the total copper weight and silver weight in each month?

I first try to first calculate the copper and silver weight each line item by creating a new measure:

sum(delivery quantity)*[copper weight / part]

then I have difficulty to have a sum of each month based on this formel.

I tried then the function:

sum(Aggr(Sum([Delivery Quantity])*[copper weight / part], [Supplier],[Part]))

But it gives me wrong figure in each month if I show them in a pivot table, however the total sum of all months is correct. Only if I select the exact month, I get the correct value. Why???

Really hope I can get a quick answer.

Table 1: Part master data

 Part Supplier Copper weight / part Silver weight / part x A 0,1 0,002 y B 0,006 z C 0,4 0,004 w C 0 0

Table 2: delivery quantity each month

 Part Supplier Month Delivery quantity x A Oct 1 x A Nov 2 x A Dec 1 y B Oct 1 y B Nov 1 y B Dec 4 z C Oct 2 z C Nov 1 z C Dec 8 w C Oct 10 w C Nov 12 w C Dec 13
5 Replies
MVP

Re: urgent help: how to calculate the sum of a calculated sum

What happens if you add Month to the Aggr dimension

Sum(Aggr(Sum([Delivery Quantity])*[copper weight / part], [Supplier],[Part], Month))

Valued Contributor

Re: urgent help: how to calculate the sum of a calculated sum

If your dimensions in Pivot table is Supplier, Part and month

Then only this expression should work 'Sum([Delivery Quantity])*[copper weight / part]'.

If not working than I am missing something.

New Contributor II

Re: urgent help: how to calculate the sum of a calculated sum

This gives me only when I select a certain part. Because each part has different copper weight and silver weight, it isn't able to give me a total.

New Contributor II

Re: urgent help: how to calculate the sum of a calculated sum

Till doesn't give me the right answer...same result as my function above.

MVP

Re: urgent help: how to calculate the sum of a calculated sum

Based on the data you have provided above, what is the expected output you are looking to get?