topic Re: How to make Grand total zero in Straight Total in QlikView

How to make Grand total zero in Straight Total

Wed, 23 Nov 2016 08:40:55 GMT

Hi All,

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

Here is the scenario with example:

Let us say, in the below table total is 640 for the current Month Nov 16 but we have to show the total as zero.

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

for the Nov Month I am storing the total value in one of the variable i.e

Set vTotalValue=Sum(Expression);

and showing the total value against the dimesnion Say 'E' and change the sign of value of variable by multiplying with -1 to make it zero.

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

Now issue with the total making it zero in Dec Month suppose in the month of Dec Dimension ‘E’ added with Expression value i.e. 500 so now the Total is 1140 so we have to make Total zero, the requirement is we will add to a total 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 for Dec Month :

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

In the above table we have to add -1140 to the total to make it zero. How to achieve this in Qlikview Straight Table?

Any help is appreciated!!! Please help it is urgent.

Re: How to make Grand total zero in Straight Total

stevedark — Wed, 23 Nov 2016 14:01:04 GMT

Hi Deepak,

I'm not sure I quite understand what you are trying to acheive, but this code may work for you:

Sum(Expression) + (if(MaxString(Dimension) = 'E', sum(TOTAL Expression) * -1, 0)) + (sum({1<Dimension={'E'}>}Expression) * 0)

This will put the inverse of the value across all dimensions on to E, regardless of whether E has a value or not.

I'm not sure if the final part (in bold) is required, but if you have a month in which there is nothing against Dimension E it may not show unless you use that set analysis. The multiply by zero is so that no value actually appears - it just forces the row to show.

Steve

Re: How to make Grand total zero in Straight Total

Anonymous — Wed, 23 Nov 2016 14:15:40 GMT

Hi,

See attached.

Total Mode > Sum of rows

Hope this helps!

...Updated with attachment!