I have a Straight Table which displays current and previous stock for every Product with its location. Item and Location are dimensions and Current stock and Previous Stock are expressions based on the input dates by the user.
Prod Loc Current Stock Previous Stock
1 A 10,000 -
1 B 20,000
1 B 15,000
2 A 7,000 -
2 B 12,000
2 B 10,000
Now I want to display the table with difference stock (Current Stock - Previous Stock) for the respective Product and location. Like this:
Prod Loc Current Stock Previous Stock Difference Stock
Generally speaking, you need to look at functions like above() or before() to do something like this.
I'm wondering, however, how can you get two separate rows for the same Product and Location, where one row only shows Current Stock and the other row only shows Previous Stock?
If you used aggregation functions like sum(), min(), etc... , you would get a single row with both numbers filled out. To get two separate rows, you must be using hidden dimensions, or not using aggregation in your expressions.
My point is - if you don't need to split your presentation into two separate rows, then your calculation could be much simpler...
If your data is shown in QlikView as your example, I suggest you do a group by Prod and Loc and then you can get the difference using a preceding load or in the straight table.
I suggest the next:
LOAD
*,
[Current Stock] - [Previous Stock] as [Difference Stock];
