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

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

Try

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

[beck_bakytbek]

try this:

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

[Anonymous]

Do you have a sample QVW?

[Anonymous]

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

[beck_bakytbek]

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

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

[Anonymous]

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.

[Anonymous]

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

[sunilkumarqv]

Hi mats,

Please try below

as Denominator

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

or

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