topic Re: Help with Running Total in Line Chart in App Development

Help with Running Total in Line Chart

Thu, 05 Jan 2017 17:26:43 GMT

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!

Re: Help with Running Total in Line Chart

sunny_talwar — Thu, 05 Jan 2017 17:38:41 GMT

Sample would be great, can you upload one please

Re: Help with Running Total in Line Chart

Thu, 05 Jan 2017 18:24:01 GMT

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 (:

Re: Help with Running Total in Line Chart

sunny_talwar — Thu, 05 Jan 2017 18:35:43 GMT

Is this what you want to see?

Expression:

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

Re: Help with Running Total in Line Chart

Thu, 05 Jan 2017 20:39:40 GMT

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.

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

Re: Help with Running Total in Line Chart

sunny_talwar — Thu, 05 Jan 2017 20:53:50 GMT

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)))

Re: Help with Running Total in Line Chart

Fri, 06 Jan 2017 19:07:22 GMT

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.

Re: Help with Running Total in Line Chart

sunny_talwar — Fri, 06 Jan 2017 19:52:01 GMT

This?

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))))

Re: Help with Running Total in Line Chart

Sat, 07 Jan 2017 18:43:52 GMT

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!

Re: Help with Running Total in Line Chart

sunny_talwar — Sat, 07 Jan 2017 19:14:18 GMT

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.

Re: Help with Running Total in Line Chart

Sat, 07 Jan 2017 19:43:25 GMT

Check top right table of attached app. Notice how measures are all null. Now filter for part 50-46PQ and then you start seeing the dates and cumulative values where it's < 0. Even thought part is a column, it doesn't group by part unless you select one.

Keeping the filter on, notice how measure 2 now shows up in the line chart, but it doesn't when there is no part filter selected.

Re: Help with Running Total in Line Chart

sunny_talwar — Sat, 07 Jan 2017 20:06:18 GMT

I think the chart on the top right can be fixed using this?

If(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)),

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

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

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)),

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

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

But how that play a role in the main chart, I still don't understand.... but may be this....

RangeSum(Column(1),

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

RangeSum(

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

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

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

), 0, RowNo())) < 0,

RangeSum(Above(

RangeSum(

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

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

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

), 0, RowNo()))), partno, (reqdate,(Numeric, Ascending)))) * -1)

Re: Help with Running Total in Line Chart

Sat, 07 Jan 2017 21:16:53 GMT

Afraid that still didn't fix it. If you filter for part 50-46PQ, you see that there's a negative value at 1/11/2017, but if we export the table with no filter, search for the same part, there is no negative value on this date.

My goal for the 2nd measure is to use the cumulative total for the sum of all parts, which is measure1, and then look at every individual part on every date, and if any individual part has a cumulative qty of < 0, I want to take that qty, times it by -1, and then add it to the cumulative total on that date.

That will make measure1 show the cumulative and measure2 show what should be the cumulative total if we're to meet all the part demands. I hope this clarifies things a little further. I really do appreciate all your help (:

Re: Help with Running Total in Line Chart

sunny_talwar — Sat, 07 Jan 2017 21:29:36 GMT

I see a negative value for part 50-46 PQ, is this not right?

What am I missing here? Attaching the Excel file, may be you can show me what you want in the Excel file.

Re: Help with Running Total in Line Chart

sunny_talwar — Sat, 07 Jan 2017 22:04:37 GMT

When you get time, add me as a connection or send me a private message. I have added you as a connection which gives you the option to see me private message, but since you are not my connection, I cannot send you a private message.

Best,

Sunny

Re: Help with Running Total in Line Chart

Sat, 07 Jan 2017 23:48:03 GMT

I've made much progress with the app, but ran into an issue here. If you filter for Formula, measure2 should flatline at 0 once it starts going into the negatives, but unfortunately it keeps trickling down with it. However, if you remove partno from measure2, that you added in your last expression, it works as it should, but then it doesn't work for the cumulative sum if you remove the filter.

Re: Help with Running Total in Line Chart

sunny_talwar — Sun, 08 Jan 2017 00:47:43 GMT

Can you try the attached... don't like the solution, but I think the way you have structured your whole requirement, this is the best I can do....