Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

How to make Grand total zero in Straight Total

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.

2 Replies
MVP & Luminary
MVP & Luminary

Re: How to make Grand total zero in Straight Total

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




qlikdeez
Valued Contributor

Re: How to make Grand total zero in Straight Total

Hi,

See attached.

Trial_balance_zero_total.PNG

Total Mode > Sum of rows

Hope this helps!

...Updated with attachment!