sum with aggr ?

[#1]

Hello Qlikview community,

how can i make a table outside a sum of a custom formula column?

This formula-based column is calculated by two dimensioned fields. My intention is to add up this column outside.

Some users recommend this formula in the forum. =sum(aggr(sum([DATA_Imps]) * UnitPrice, UnitPrice))

But on closer look is not on the calculation. See snapshot.

What thinking mistake am I doing?

Thank you!

Best regards,

[#2]

You need to replace Unit Price in the Aggr() function with the dimensions in your chart. Think of Aggr() as a Group By in SQL. You are grouping by unit price and then summing the values.

It looks like your chart has three dimensions, so you need:

=sum(aggr(sum([DATA_Imps]) * UnitPrice, OPP, Einheitspreis, Produkt))

[#3]

Hello NMiller,

can not realize your recommendation! Is there no other way to take values directly from the chart?

Thank you !

Best regards,

[#4]

Hi,

unfortunately I can not from a formula-based form field a final sum.

Explanation:

My table is calculated in every line the product of (quantity * price). Receives the product name (Produktitem) the content of 'lock', so the amount divided by 2. Otherwise not.

In sum, should appear as expected, a value of € 699,687.28.

Unfortunately I did not manage to make a sum of these lines.
I've uploaded my qvw.

Thanks for any help

Best Regards,

[#5 · Accepted solution]

First, you need to replace Einheitspreis in the Text Box expression. Einheitspreis is an expression in the chart and has no meaning outside of the chart. Use the formula for Einheitspreis instead: SAL_OPPORTUNITYLINEITEM_UnitPrice.

Then Aggr() around your existing expression with the dimensions in your chart:

=Sum(Aggr(
if( index(concat([HEL_BANNER_Banner]),'lock')>=1,
(sum([HEL_DATA_Imps mit Defaults])*0.5),(sum([HEL_DATA_Imps mit Defaults])) ) *
SAL_OPPORTUNITYLINEITEM_UnitPrice
,SAL_OPPORTUNITY_Name, SAL_PRODUCT_Name))

Place that into a Text Box and you get the 699687 you are looking for. Sample attached.

[#6]

you are my hero

Thank you very much ! Arigato wise Ninja

[#7]

My pleasure. I'm adding the quote, "Arigato wise Ninja," to my business card!

[#8]

Hi,

I review your aplictation and I think that this is the formula that you need.

=sum(aggr(

if(index(concat([HEL_BANNER_Banner]),'lock')>=1,sum([HEL_DATA_Imps mit Defaults]*0.5),sum([HEL_DATA_Imps mit Defaults]) )

*SAL_OPPORTUNITYLINEITEM_UnitPrice

,SAL_PRODUCT_Name))

Regards,

[#9]

Hi,

if I want to hold that value, I would like to add this.

sum ({1 <[HEL_DATA_Datum> YEAR] = {$ (GetFieldSelections = ([HEL_DATA_Datum> YEAR]))}>}

It should therefore be considered only when the year changes. All other changes are to be ignored.

I tried that.

=sum( {1 <[HEL_DATA_Datum> YEAR] = {$ (GetFieldSelections = ([HEL_DATA_Datum> YEAR]))}>} Aggr(

if( index(concat([HEL_BANNER_Banner]),'lock')>=1,
(sum([HEL_DATA_Imps mit Defaults])*0.5),(sum([HEL_DATA_Imps mit Defaults])) ) *
SAL_OPPORTUNITYLINEITEM_UnitPrice

,SAL_OPPORTUNITY_Name, SAL_PRODUCT_Name))

But unfortunately without success. Perhaps you have an idea?

Thank you!

[#10]

You could do something like this:

=sum({1<[HEL_DATA_Datum > YEAR]={"$(=Max([HEL_DATA_Datum > YEAR]))"}>} aggr(

if(index(concat([HEL_BANNER_Banner]),'lock')>=1,

sum({1<[HEL_DATA_Datum > YEAR]={"$(=Max([HEL_DATA_Datum > YEAR]))"}>} [HEL_DATA_Imps mit Defaults])*sum({1<[HEL_DATA_Datum > YEAR]={"$(=Max([HEL_DATA_Datum > YEAR]))"}>} SAL_OPPORTUNITYLINEITEM_UnitPrice)*0.5,

(sum({1<[HEL_DATA_Datum > YEAR]={"$(=Max([HEL_DATA_Datum > YEAR]))"}>} [HEL_DATA_Imps mit Defaults])*sum({1<[HEL_DATA_Datum > YEAR]={"$(=Max([HEL_DATA_Datum > YEAR]))"}>} SAL_OPPORTUNITYLINEITEM_UnitPrice)))

,SAL_PRODUCT_Name))

It work with 2011, but has some problems with 2010, maybe you can check the detail