Fabs(), Sum() & Total()

[Anonymous]

Dear community,

I am trying to accumulate percentages in a column for absolute values.

As you can see, the expression in the right column isn't working properly, it seems

like the TOTAL is still the Sum of the values that are not absolute.

Those are my expressions:

Sum({$< [FS Item] = {1180000}>}[Period Value GC])                (left column, values)

fabs(Sum({$< [FS Item] = {1180000}>}[Period Value GC]))       (mid column, absolute values)

Num(fabs(sum({$< [FS Item] = {1180000}>}[Period Value GC])) / fabs(sum({$< [FS Item] = {1180000}>} Total [Period Value GC])), '#,##%')                                                                                    

(right column, where I'd like to get the accumulated percentage values, it shouldn't go beyond 100%)
                                                                                                 

I think there might be problem with nested aggregation, I don't know how to fix it though.

Thank you in advance!

Best regards,

Mats

[beck_bakytbek]

Hi Mats,

can you give the whole example (i mean fake / example date), with dim and expression, then i find the solution for your

Beck

[beck_bakytbek]

or if you use the pivot table, select your dimension and select: Show Partial Sums

I hope that helps

Beck

[beck_bakytbek]

Mats,

if you use the pivot table, in tab: Dimensions and Expression > Select your Dimension > and select: Show Partial Sums, and as expression use (of course you should modify it): Sum(YourFiledExpression) / Sum(TOTAL <yourDim> yourFieldExpression)

Beck

[Anonymous]

I would like to avoid the pivot table option. I can't really provide a sample either, except for the table I've shown in the picture.

I mentioned the used expressions in the original posts, the dimension is company name (which I erased in the excel file). I would like QlikView to take the total of the 2nd column (2.155.787.303,40) as the 100% in the far right column.

Does that help?

[Anonymous]

does it help to use the column names instead of restating your expressions?

Something like

=sum([DTA Betrag]/sum(TOTAL [DTA Betrag]

[beck_bakytbek]

Hi Mats,

if you dont use the pivot table, then try to use these expressions:

1. Sum([Deferred Tax Assets]

2. Sum([Deferred Tax Assets] / Sum( Total [Deferred Tax Assets]

3. Sum([Deferred Tax Assets] / Sum( Total < Company Name >[Deferred Tax Assets])

or Sum([Deferred Tax Assets] / Sum( Total  AGGR Sum([Deferred Tax Assets]), [Company Name]))

i hope that helps

Beck

[Anonymous]

It just says error in expression, I am not sure if QlikView recognizes the fields.

Maybe I could address the columns in another way, so thanks for the tip!

[Anonymous]

Correction.  I needed a closing parenthesis.

=sum([DTA Betrag]/sum(TOTAL [DTA Betrag])

[beck_bakytbek]

Hi Mats,

i create a fake-example to show you, tell me whether this example meets your expectation

Beck