topic Re: Straight table sum of Rows value of Expression in a text object in QlikView

Straight table sum of Rows value of Expression in a text object

koushik_btech20 — Mon, 11 Dec 2017 13:51:25 GMT

Hi Everyone,

I have a demo data and app where I have Actual and Budget data. I need the last expression total (sum of Rows) value in a textobject. Is it possible through expression label or any if other way out is there ?

Regards,

Koushik

Re: Straight table sum of Rows value of Expression in a text object

stabben23 — Mon, 11 Dec 2017 13:59:33 GMT

Hi,

use this Expression in textbox

=sum(aggr(fabs(sum(Actual)-sum(Budget)),Date))

Re: Straight table sum of Rows value of Expression in a text object

stabben23 — Mon, 11 Dec 2017 14:00:45 GMT

or this if you want same format:

num(sum(aggr(fabs(sum(Actual)-sum(Budget)),Date)),'#,##0')

Re: Straight table sum of Rows value of Expression in a text object

sunny_talwar — Mon, 11 Dec 2017 14:01:30 GMT

This might work also, if you have one value per day for Actual and Budget

=Sum(Fabs(Actual - Budget))

If not, then go with stabben23‌'s solution

Re: Straight table sum of Rows value of Expression in a text object

stabben23 — Mon, 11 Dec 2017 14:06:25 GMT

It does stalwar1, and its of course a better solution than mine.

Re: Straight table sum of Rows value of Expression in a text object

sunny_talwar — Mon, 11 Dec 2017 14:07:50 GMT

Well this might just be some mocked up data and it might not work in real scenario. But ya def worth checking because it would be a good idea to avoid Aggr() if we can.

Re: Straight table sum of Rows value of Expression in a text object

stabben23 — Mon, 11 Dec 2017 14:09:34 GMT

agree, aggr should be avoid if possible.

Re: Straight table sum of Rows value of Expression in a text object

koushik_btech20 — Mon, 11 Dec 2017 14:15:53 GMT

Based on Steffan solution I was trying to resolve but getting 0 where in dimension I am using TimeDim cyclic group and actual and budget this two expressions I am storing in a variable which I am referring.

I am using below expression,

sum(aggr(fabs($(expr_Actual)-$(expr_Budget)),GetCurrentField(TimeDim)))

Re: Straight table sum of Rows value of Expression in a text object

stabben23 — Mon, 11 Dec 2017 14:25:54 GMT

Hi,

I dont know why you are using GetCurrentField in a textbox?

try Without that,

or Sum(Fabs($(expr_Actual)-$(expr_Budget)))

Re: Straight table sum of Rows value of Expression in a text object

koushik_btech20 — Mon, 11 Dec 2017 14:29:59 GMT

It's throwing an error as nested aggregation not allowed.

Re: Straight table sum of Rows value of Expression in a text object

sunny_talwar — Mon, 11 Dec 2017 14:32:34 GMT

May be try this

Sum(Aggr(fabs($(expr_Actual)-$(expr_Budget)), $(='[' & GetCurrentField(TimeDim) & ']')))