Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Hi,
See attached.
Total Mode > Sum of rows
Hope this helps!
...Updated with attachment!