Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
masdler1
Contributor II
Contributor II

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

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.

Labels (2)
1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

10 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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
Contributor II
Contributor II
Author

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

marcus_sommer
MVP & Luminary
MVP & Luminary

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
Contributor II
Contributor II
Author

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
MVP & Luminary
MVP & Luminary

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
Contributor II
Contributor II
Author

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

marcus_sommer
MVP & Luminary
MVP & Luminary

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
Contributor II
Contributor II
Author

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

marcus_sommer
MVP & Luminary
MVP & Luminary

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