topic Re: Fabs(), Sum() & Total() in QlikView

Fabs(), Sum() & Total()

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Fabs(), Sum() & Total()

beck_bakytbek — Tue, 16 May 2017 14:13:44 GMT

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

Re: Fabs(), Sum() & Total()

beck_bakytbek — Tue, 16 May 2017 14:21:07 GMT

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

I hope that helps

Beck

Re: Fabs(), Sum() & Total()

beck_bakytbek — Tue, 16 May 2017 14:30:02 GMT

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

Re: Fabs(), Sum() & Total()

Anonymous — Tue, 16 May 2017 14:36:14 GMT

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?

Re: Fabs(), Sum() & Total()

Anonymous — Tue, 16 May 2017 14:42:37 GMT

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

Something like

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

Re: Fabs(), Sum() & Total()

beck_bakytbek — Tue, 16 May 2017 14:49:05 GMT

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

Re: Fabs(), Sum() & Total()

Anonymous — Tue, 16 May 2017 14:53:43 GMT

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!

Re: Fabs(), Sum() & Total()

Anonymous — Tue, 16 May 2017 15:03:47 GMT

Correction. I needed a closing parenthesis.

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

Re: Fabs(), Sum() & Total()

beck_bakytbek — Tue, 16 May 2017 15:06:09 GMT

Hi Mats,

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

Beck

Re: Fabs(), Sum() & Total()

Anonymous — Tue, 16 May 2017 15:08:33 GMT

They only thing that doesn't seem to work is calculating a total of absolute values, this part of the last expression:

fabs(sum({$< [FS Item] = {1180000}>} Total [Period Value GC])

I think I might need the ,,Aggr()" function somewhere.

Re: Fabs(), Sum() & Total()

Anonymous — Tue, 16 May 2017 15:11:02 GMT

Hi Wallo,

QV doesn't recognize the field names. I can build the expression with column(),

like this: =sum(column(2))/sum(TOTAL column(2)), it doesn't give me any values though.

Thanks for your help.

Re: Fabs(), Sum() & Total()

Anonymous — Tue, 16 May 2017 15:13:05 GMT

Try

sum(TOTAL {$< [FS Item] = {1180000}>} fabs([Period Value GC]))

Re: Fabs(), Sum() & Total()

beck_bakytbek — Tue, 16 May 2017 15:14:59 GMT

try this:

Sum(YourExpression)/Sum(total aggr (Sum (YourExpression),YourDimension))

Re: Fabs(), Sum() & Total()

Anonymous — Tue, 16 May 2017 15:19:34 GMT

Do you have a sample QVW?

Re: Fabs(), Sum() & Total()

Anonymous — Tue, 16 May 2017 15:29:13 GMT

none of these work, unfortunately
thanks for your help so far

Re: Fabs(), Sum() & Total()

beck_bakytbek — Tue, 16 May 2017 15:32:34 GMT

As i know, you have only one dimension: Company Name,

it should work? what do you use : Straighttable or PivotTable?

Re: Fabs(), Sum() & Total()

Anonymous — Tue, 16 May 2017 15:45:23 GMT

I am using a straight table.

I've created a sample.

The expressions work, for some reason. It doesn't work when I include set expression though.

Re: Fabs(), Sum() & Total()

Anonymous — Tue, 16 May 2017 15:57:31 GMT

Try this. You could hide any columns you want in the straight table after.

Re: Fabs(), Sum() & Total()

sunilkumarqv — Wed, 17 May 2017 04:34:50 GMT

Hi mats,

Please try below

as Denominator

Sum(Total aggr(Fabs(sum({$< [FS Item] = {1180000},DimensionValue=>} Total [Period Value GC])),DimensionValue))

Sum(Total aggr(Fabs(sum(<DimensionValue>{$< [FS Item] = {1180000},DimensionValue=>} Total [Period Value GC])),DimensionValue))