Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
robinwiman
Creator
Creator

How to make columns subtract from the prior one

I'm trying to create a table for our logistics department where they want to see how we predict stock levels to decrease and eventually go to zero (0) - if we don't refill of course.

So I have a table where I have the following columns:

Column 1: Name of the store

Column 2: Current stock level this week

Column 3 and forward (one for each week): Current stock level subtracted with forecasted sales.

So column 3 would be: Sum ([Column2] - QtyBu) pretty much.

And column 4 would be Sum ([Column3] - QtyBu) etc. until we enter into the negative stock levels.

Can I create a feature that weekly subtracts what is expected to be sold against what was last week's stock?


What I am really asking, I guess, is if it is possible to do the weekly subtraction in a single expression.


I have all the neccessary dimensions, variables and tools to do it. I just don't know how.

5 Replies
swuehl
MVP
MVP

How does your data model look like,i.e. how is your stock level and forecast stored?

Maybe create a common time dimension for your stock and forecast.

Then use a pivot table with store name vertical and time dimension starting  this week and going an appropriate period into the future, pivoted to the top / horizontal.

Your expression could look like

=Rangesum( Sum(TOTAL<Store> {<ThisWeek = {1} >} Stock), -Before( Sum(QtyBu) ,0, ColumnNo() ) )

Basically use chart inter record functions like Before() / Above() in combination with a Rangesum() function to aggregate all weeks forecast up to the dimensional week.

shraddha_g
Partner - Master III
Partner - Master III

try sum(column(2)-QtyBu)

Else

Column(2) - Sum(QtyBu)

robinwiman
Creator
Creator
Author

I know this method, and it would've worked if I only had two columns. But you can't use this method if you have more than 1 column.

robinwiman
Creator
Creator
Author

Stock level and forecast can be shown on a daily basis and per store.

We have lots of time dimensions like this week, prior week, next week, this week last year etc.

I'm going to try the function you suggest and report back thanks

robinwiman
Creator
Creator
Author

I should mention that I am using a dimension called "Year Week", which is basically what it says. This week for the current year.

I would like to show the table columns as:

Current stock level | This week (40) | 2017 w41 | 2017 w42 etc...