Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with Running Total in Line Chart

First time post, long time viewer.

Hey everyone, I've been getting advice from you great people for awhile, but now I have an issue that's a little more specific to my environment. I have an inventory system which has about 60k parts in it. My goal here is I'm trying to show a sum of the running total of part quantity on hand, with the addition of purchase orders and the subtraction of sales orders related to these parts. I'm trying to put this into a line chart, with the ReqDate as the dimension, so I can see when inventory is going up and down.

It may be a little easier if I explain like this:

Table1_OH:

Partno    Qty -(This is loading on hand amount in system)

    A          30

    B          25

    C          50

Table2_SO:

SalesOrder    Partno    Qty    ReqDate -(Date Qty will be subtracted from Running Total)

      1                    A          10          1/22/2017

      2                    B           5           1/17/2017

      3                    C          20          2/03/2017


Table3_PO:

PurchaseOrder     Partno    Qty    ReqDate -(Date Qty will be Added to Running Total)

           1                       A           5           2/14/2017

           2                       B          15          2/05/2017

           3                       C          25          3/12/2017

Now obviously my data is on a much bigger scale, but the premise is mostly the same. I believe I have an issue with there being no ReqDate associated when I load the Table1_OH. I've tried load Date(today()) as reqdate on that table and using RangeSum function in line chart, but then the on hand sums go whacky on dates where Sales Orders or Purchase Orders hit the system.

Sorry guys, I know this is lengthy. I can upload a sample of what I've got if anyone needs.

Thanks in advance!

16 Replies
sunny_talwar

Sample would be great, can you upload one please

Not applicable
Author

Added sample to original message. Don't rely on what's in the measure field of the line chart. I was testing many different solutions (:

sunny_talwar

Is this what you want to see?

Capture.PNG

Expression:

Aggr(RangeSum(Above(Sum(Aggr(Sum(DISTINCT OH), partno)), 0, RowNo())), (reqdate,(Numeric, Ascending)))

Not applicable
Author

That's getting closer, but that is only looking at the OH and I need to show the sum of OH + qtyord - qtyneed in a cumulative line chart. I'm not sure this is an accurate start to that, as this is showing and increase in OH, yet qtyord or qtyneed hasn't been added to the Expression yet.

The OH should only be showing what's currently in inventory from the date of the load and should theoretically be flat in the line chart, as nothing else is affecting it yet.

I've thought about joining the tables, then trying the loads and I think I'm starting to get somewhere. See the top 3 tables, Left is On Hand and is loaded Date(today()) as reqdate, so now I have the sum and a beginning date. Middle are purchases and the dates they need to be added to On Hand, and Right are sales and dates they need to be removed from the system.

Associated with each purchase order and sales orders are a few parts and qtys, so adding partno to the middle and right table will break down what parts are making up the sum.

thisproj.PNG

Now I just need to get this into an accumulative line chart at the bottom if you know an Expression for that?

-New app attached to main question

sunny_talwar

May be like this?

Aggr(RangeSum(Above(

  RangeSum(

  Sum(Aggr(Sum(DISTINCT OH), row1)),

  Sum(Aggr(Sum(DISTINCT qtyord), partno)),

  -Sum(Aggr(Sum(DISTINCT qtyneed), wono, partno))), 0, RowNo())), (reqdate,(Numeric, Ascending)))


Capture.PNG

Not applicable
Author

It's getting much closer, but unfortunately once you click a date past today, it starts the On Hand Inventory at 0, instead of what it was at the end of the previous date. I'm assuming this is because I added Date(today()) as reqdate in my On Hand table, so once we clicked a date that was outside of that date, it will return with a 0 amount of On Hand to start with.

It seems like the only way to really do this is to load this accumulative sum in the data load. That way, when a date is selected, we see what the inventory amount should be like on that date.

I've attached what I've got so far. with your very helpful expression in the line chart. Notice though, when we click a date that isn't today, the OH amount changes to 0.

sunny_talwar

This?

Capture.PNG

Dimension

reqdate

Expression

Only({<reqdate = {"=reqdate < Date(Today()+120)"}>} Aggr(RangeSum(Above(

  RangeSum(

  Sum({<reqdate>}Aggr(Sum({<reqdate>} DISTINCT OH), row1)),

  Sum({<reqdate>}Aggr(Sum({<reqdate>} DISTINCT qtyord), purno,partno)),

  -Sum({<reqdate>} Aggr(Sum({<reqdate>}DISTINCT qtyneed), wono, partno))

        ), 0, RowNo())), (reqdate,(Numeric, Ascending))))

Not applicable
Author

Unfortunately there's been another request for this app and I've been trying to no avail to get it done. Your previous expression has worked as needed, so I'll be marking you answer Helpful regardless. However, I was wondering if you could help me just one step further? Currently the chart is a cumulative sum of OnHand + QtyOrd - QtyNeed for all Parts. What I've just been asked is if we can add another line to the chart showing if an individual Part has a cumulative sum < 0, take that sum * -1 and add it to the original expression above. Doing this should make the 2nd line as basically a type of forecast of what we should have and help us tackle issues.

I've tried doing the cumulative sum in the Resident Load without success. Then I tried using your expression in a table with Reqdate, so i could export and then import the sums as a temporary fix, but it doesn't group by individual Part.

Any ideas?   Thanks again for all your help!

sunny_talwar

I think you need to show me an example using two parts where one the cumulative sum <0 and other cumulative sum >0 and how the whole new expression needs to look like.