topic sum with aggr ? in QlikView

sum with aggr ?

Tue, 22 Feb 2011 21:01:07 GMT

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,

sum with aggr ?

Tue, 22 Feb 2011 21:11:09 GMT

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))

sum with aggr ?

Wed, 23 Feb 2011 16:02:04 GMT

Hello NMiller,

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

Thank you !

Best regards,

sum with aggr ?

Wed, 23 Feb 2011 16:25:21 GMT

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,

sum with aggr ?

Wed, 23 Feb 2011 17:51:27 GMT

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.

sum with aggr ?

Wed, 23 Feb 2011 17:56:14 GMT

you are my hero

Thank you very much ! Arigato wise Ninja

sum with aggr ?

Wed, 23 Feb 2011 18:10:47 GMT

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

sum with aggr ?

Wed, 23 Feb 2011 18:18:06 GMT

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,

sum with aggr ?

Wed, 23 Feb 2011 19:01:49 GMT

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!

sum with aggr ?

Wed, 23 Feb 2011 20:12:27 GMT

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

sum with aggr ?

Wed, 23 Feb 2011 20:46:59 GMT

Hi Octavio,

hmmm, unfortunately it's not what I'm looking for.

Thank you

Best Regards,

sum with aggr ?

Wed, 23 Feb 2011 22:43:51 GMT

Hi NMiller,

can you help me with my problem ?

Thank you !

sum with aggr ?

Wed, 23 Feb 2011 22:50:13 GMT

You need to add an equals sign and remove the space after the dollar sign in your dollar sign expansion. Maybe:

=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))

For dollar sign expansions (when using expressions), use (no spaces after the dollar sign, I think that has been shown to cause errors):

$(=...)

Also, is your field called HEL_DATA_Datum> YEAR? That greater than sign is throwing me off, but if that is the name, then it should work.

sum with aggr ?

Wed, 23 Feb 2011 23:21:45 GMT

Hi,

this is really weird! I already have a text box, which was defined as follows.

####
='Totalprice (TOTAL) PLAN: ' & sum ({1< [HEL_DATA_Datum > YEAR] = {$(=GetFieldSelections([HEL_DATA_Datum > YEAR]))} >} SAL_OPPORTUNITYLINEITEM_TotalPrice )
####

This works as required. The displayed value does not change when I click on a field somewhere else. Except for the [HEL_DATA_Datum> YEAR].

Exactly the same function I need for the text box also.

But your proposed code for the text box changes as soon as I make a selection elsewhere. But it should only change if I have a Selekttion in Field [HEL_DATA_Datum> YEAR].

Thank you for your help.

Best Regards,

sum with aggr ?

Thu, 24 Feb 2011 16:09:56 GMT

In my proposed expression, there are Sums without the {1} Set Analysis clause. In your working expression, every (only one) aggregate function has the {1} to ignore selections.

When using Aggr() and multiple aggregate functions (Sum), you have to be careful where you put the Set Analysis. I don't have a definitive rule for how it works; most of the time I just use guess and check. In this case, I would guess:

=sum({1<[HEL_DATA_Datum> YEAR]={$(=GetFieldSelections([HEL_DATA_Datum> YEAR]))}>}
 Aggr(if( index(concat([HEL_BANNER_Banner]),'lock')>=1,
 (sum({1}[HEL_DATA_Imps mit Defaults])*0.5),
 (sum({1}[HEL_DATA_Imps mit Defaults])) ) *
 SAL_OPPORTUNITYLINEITEM_UnitPrice
,SAL_OPPORTUNITY_Name, SAL_PRODUCT_Name))

You may also need to put the GetFieldSelections inside the inner sums, but I'm not sure. You may be able to put it in the inners and not the outer Sum. Try different combinations until you get the answer you want.

sum with aggr ?

Thu, 24 Feb 2011 17:48:36 GMT

Hi Wise Ninja

Unfortunately, I'm not handle with this aggregate function [:'(] No matter how I twist or turn, it is not what I expect. I thank you anyway and your standby. You are really cooperative and cool.

I am still at the beginning and needs to learn a lot.

Sincerely,