topic Re: Absolute sum at the dimension level in a pivot table in Qlik Sense in App Development

Absolute sum at the dimension level in a pivot table in Qlik Sense

LucyN — Wed, 19 Aug 2020 22:02:43 GMT

Hi,

I'm totally new to Qlik Sense. I have a pivot table with 2 dimensions: Ccy Pair and Account. I'm making a pivot table where the values have signs, but I want the sum at the Account level to be absolute value (please see the screenshot below). Is there any settings or function that can serve this purpose?

Re: Absolute sum at the dimension level in a pivot table in Qlik Sense

Kushal_Chawda — Thu, 20 Aug 2020 11:33:28 GMT

Assuming sum(Amount) is your measure. You can replace with your actual measure

=if( Dimensionality()=1,
fabs(sum(aggr(Sum(Amount),Ccy_pair))),Sum(Amount))

Re: Absolute sum at the dimension level in a pivot table in Qlik Sense

LucyN — Thu, 20 Aug 2020 18:52:07 GMT

Hi Kush,

Thank you! I got absolute total at the Ccy Pair level, which is great! But the Totals still show signed (negative) numbers. Is there any way to make Totals = sum of all absolute totals of Ccy Pairs?

Re: Absolute sum at the dimension level in a pivot table in Qlik Sense

LanBiteam — Wed, 21 Oct 2020 14:08:13 GMT

Hello Lucy,

I wonder did you get the answer to the Total value? How could it also be shown in absolut value?

Thank you.

Lan

Re: Absolute sum at the dimension level in a pivot table in Qlik Sense

Kushal_Chawda — Wed, 21 Oct 2020 14:29:59 GMT

@LucyN try below

=if( Dimensionality()=1,
fabs(sum(aggr(Sum(Amount),Ccy_pair))),

if( Dimensionality()=0,
sum(aggr(fabs(sum(aggr(Sum(Amount),Ccy_pair))),Ccy_pair)),

Sum(Amount)))