16 Replies Latest reply: Jan 7, 2017 7:47 PM by Sunny Talwar RSS

    Help with Running Total in Line Chart

    Thomas Westberry

      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

          Sample would be great, can you upload one please

          • Re: Help with Running Total in Line Chart
            Thomas Westberry

            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

                Is this what you want to see?

                 

                Capture.PNG

                 

                Expression:

                 

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

                  • Re: Help with Running Total in Line Chart
                    Thomas Westberry

                    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

                      • Re: Help with Running Total in Line Chart
                        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

                          • Re: Help with Running Total in Line Chart
                            Thomas Westberry

                            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

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

                                  • Re: Help with Running Total in Line Chart
                                    Thomas Westberry

                                    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

                                        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
                                            Thomas Westberry

                                            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

                                                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)

                                                 

                                                Capture.PNG

                                                  • Re: Help with Running Total in Line Chart
                                                    Thomas Westberry

                                                    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

                                  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
                                    Sunny Talwar

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