topic Re: How to Make Total as Zero in QlikView

How to Make Total as Zero

Fri, 18 Nov 2016 10:44:57 GMT

Hi All,

We are building the Trail Balance Report in straight table and we have to make the sum of straight table Expression value to Zero by adding the appropriate amount dynamically at the end.

Here is the scenario with example:

Let us say, in the below table total is 640 but we have to show the total as zero.

Dimension	Expression
A	100
B	200
C	220
D	120
Total:	640

So, the requirement is we will be given a constant value for a dimension (Let us say it is E here) and whenever the total is not zero in the table we have to add the appropriate value dynamically against the dimension (Value: E) to make it zero. Below is the desired output:

Dimension	Expression
A	100
B	200
C	220
D	120
E	-640
Total:	0

In the above table we have to add -640 to make it zero. How to achieve this in Qlikview Straight Table? Any help is appreciated!!! Please help it is urgent.

Regards,

Deepak

Re: How to Make Total as Zero

stigchel — Fri, 18 Nov 2016 11:13:03 GMT

Try with

=if(Dimension='E',Sum(TOTAL Expression)*-1,Sum(Expression))

and set the total mode of the expression to Sum of rows

Re: How to Make Total as Zero

Frank_Hartmann — Fri, 18 Nov 2016 11:15:19 GMT

have a look at the attached sample.

hope this helps

Re: How to Make Total as Zero

enriquem — Fri, 18 Nov 2016 11:43:17 GMT

Hi,

Why you don't try to use variables??

You can use a variable like:

SET totalValue = ''-' & =Sum(Expression)';

Hope this help.