How to do the following: sum(sum(Field A) * avg(Field B)) in Qlik?

[masdler1]

Hi,

I have two fields: A and B. I need to sum all the values in Field A and multiply it by the average value of Field B so basically sum(sum(Field A) * avg(Field B)). When im trying to do this in qlik I get an error. How could I achieve this? Thanks in advance.

[marcus_sommer]

Nesting aggregations could be done with aggr() to specify the needed dimensionally context for the calculation. This means something like:

sum(aggr(sum(Field A) * avg(Field B), Dim1, Dim2))

- Marcus

[masdler1]

Thanks, can I use Field A as Dim1 and Field B as Dim2?

[marcus_sommer]

It depends - but rather not. Field A/B are usually measures likes Sales or Amount and Dim 1/2 are normally dimensions like Products or Stores or Periods - mostly the dimensions from the chart and/or a more granular ones - for example if the calculation should be done on a date-level but the chart displayed only YearMonth - and sometimes also on any of the made selections.

- Marcus

[masdler1]

I see, im trying to get this to work with previous year so that the expression would be calcualted based on previous year's values and it would be used in a dashboard / text object (not a table). So far I have this:

sum({$<Year={$(=Year-1)}, Month=>}aggr(sum(Field A) * avg(Field B), Year, Month))

but it shows 0?

[marcus_sommer]

I think I would try it with:

sum(aggr(
   sum({$<Year={$(=max(Year)-1)}, Month=>} [Field A]) *
   avg({$<Year={$(=max(Year)-1)}, Month=>} [Field B]),
Year, Month))

- Marcus

[masdler1]

Thank you for the suggestion. Unfortunately it's still showing 0.

[marcus_sommer]

In such a case I would check each single part, like:

sum({$<Year={"$(=max(Year)-1)"}, Month=>} [Field A])
$(=max(Year)-1)
avg({$<Year={"$(=max(Year)-1)"}, Month=>} [Field B])

Does all work like expected?

- Marcus

[masdler1]

Hi, I checked all parts and they show correct values. Problem comes while multiplying them for some reason.

[marcus_sommer · Accepted solution]

It's difficult to say what might be the cause. Thinkable would be that [Field A] and [Field B] have no proper association to each other respectively in regard to the Year and Month.

To get more insights you may use a table-chart with Month as dimension and the following expressions:

sum({$<Year={"$(=max(Year)-1)"}>} [Field A])
sum([Field A])
avg({$<Year={"$(=max(Year)-1)"}>} [Field B])
avg([Field B])
sum({$<Year={"$(=max(Year)-1)"}>} [Field A]) * avg({$<Year={"$(=max(Year)-1)"}>} [Field B])

Again everything showed the expected results?

- Marcus