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]

Hi Octavio,

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

Thank you

Best Regards,

[#3]

Hi NMiller,

can you help me with my problem ?

Thank you !

[#4]

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.

[#5]

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,

[#6]

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.

[#7]

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,