1 2 Previous Next 20 Replies Latest reply on Jun 18, 2018 10:28 AM by Zach DeMascole

    Calculating Delta between Current Balance and Prior Year-End Balance and Combine Tables

    Zach DeMascole

      Hello everyone,

       

      I am nearing completion with a project I am working on and could use one last piece of help from you experts out there!

       

      I would like to create a table in Tableau that looks like this.  "Inventory - Actuals" represents the current month of actuals:

      Balance Table.PNG

       

      [Prior Year End Values were:  Balance = $3,900;  DIO = 87]

       

       

      I think I have gotten somewhat close, but have two issues that I can't figure out and I couldn't find any previously answered questions on these specific topics:

       

      1.  My "Year to Date Delta" row is not correct in Tableau.  (In the workbook, the field is called "zzz Prior Year End Balance Delta").  The calculation for this field was based on an answer I got from another post I made to the forum.  The calculation worked for that particular instance, but when applied here it doesn't.  In Tableau, the calculation seems to be subtracting 0 from the current balance rather than subtracting the prior year-end balance.

       

      2.  I have two separate tables in Tableau right now (1 with a "Balance" column and 1 with a "DIO" column) and I can't figure out how to combine them into 1 table like in the image above.  Any ideas?

       

      I have mocked up the issues I am having in the attached workbook, as well as the sample data used.

       

      Please let me know if you have any clarifying questions.

       

      Thanks everyone in advance!!!

        • 1. Re: Calculating Delta between Current Balance and Prior Year-End Balance and Combine Tables
          Jennifer VonHagel

          Hi Zach,

           

          First, you can categorize your Inventory categories to separate out DIO then put all the others in an "Other" bucket. Easy to show in same table this way.

           

          Then, I looked at the calcs within zzz Prior Year End Balance Delta, and found the Prior Year Inventory Balance is returning NULL. I believe this is because the Date1 Filter is set to Apr 2018, it is filtering out Dec 2017 values.

          If we make this a FIXED formula, the FIXED will force it to ignore the Date1 filter. Buuuuut, now it is also ignoring the Program1 = Cars filter, it is summing for all Program1 types.

          To fix this, right click on the Program1 Filter, and choose Add to Context. This means FIXED formulas will take the filter into account.

          Workbook is attached, hope this helps.

          Jennifer

          1 of 1 people found this helpful
          • 2. Re: Calculating Delta between Current Balance and Prior Year-End Balance and Combine Tables
            Zhouyi Zhang

            Hi, Zach

             

            To your 1st question, please see my solution below

             

             

            To your 2nd question, can you explain a bit more?

             

            Hope this helps

             

            ZZ

            1 of 1 people found this helpful
            • 3. Re: Calculating Delta between Current Balance and Prior Year-End Balance and Combine Tables
              Bryce Larsen

              Hi Zach DeMascole,

              I went for a slightly different approach without LOD expressions, but likewise required changing the date filter. Let me preface this by stating it may depend on whether or not you need the Date Selector to be a quick filter or if it can be a parameter. I opted for a Parameter here.

               

              In the end, final date filter used:

              CF_RelevantDates - filter on TRUE:

              [CF_PriorYearEnd] OR [CF_ReportDate]

               

              This is key. Then I put categories into a Group which I could use as column header. Using new date calcs mentioned above I could calculate accordingly to produce the following:

              Screen Shot 2018-05-17 at 9.32.00 PM.png

               

              Workbook is attached for your reference!

               

              Let me know if any questions/issues!

              1 of 1 people found this helpful
              • 4. Re: Calculating Delta between Current Balance and Prior Year-End Balance and Combine Tables
                Zach DeMascole

                Jennifer VonHagel

                Zhouyi Zhang

                Bryce Larsen

                 

                Thank you all for input!

                 

                Bryce - I appreciate you pointing out that the approach you showed would not enable a quick filter by month.  That is one direction I would like to take this particular dashboard in the future (being able to select a month and results change (YTD, Current, etc.)) so I went with the approach that Jennifer and Zhouyi shared.

                 

                Jennifer and Zhouyi - Had a question about your responses.

                In Jennifer's solution, she used "{FIXED [Inventory Category]......" whereas Zhouyi's solution did not have [Inventory Category] in the formula, but both solutions appear to have selected "Add to Context" for the Program1 filter.

                 

                What is the difference between these two solutions?

                 

                 

                Zhouyi - my second question was about having in the same table, a column for "Inventory Balance" and another for "DIO".

                The items chosen in "Inventory Balance" are a subset of all of the options in "Inventory Category", one of which is DIO.  There are options within "Inventory Category" that are not part of the balance or the DIO.

                Right now I just have to two separate tables.  And on my dashboard I am hiding the row titles and putting the tables next to each other.

                 

                Thanks,

                Zach

                • 5. Re: Calculating Delta between Current Balance and Prior Year-End Balance and Combine Tables
                  Bryce Larsen

                  Hi Zach DeMascole

                  Glad to hear you're getting there!

                  FWIW, you could use another parameter:

                  • Most Recent Month (default)
                  • Custom (date picker displays)

                   

                  Then modify the custom date picker the user select using DATETRUNC and the like to match your data. I like to avoid quick filters were possible due to performance gains. Granted, depends of course on your data!

                   

                  Best of luck!

                  1 of 1 people found this helpful
                  • 6. Re: Calculating Delta between Current Balance and Prior Year-End Balance and Combine Tables
                    Zhouyi Zhang

                    Hi, Zach

                     

                    Please find my completed solution attached. Below is screenshot for your reference.

                    1st, union your data to itself

                    Create calculation fields

                     

                    And result

                     

                     

                    Hope this helps

                     

                    ZZ

                    1 of 1 people found this helpful
                    • 7. Re: Calculating Delta between Current Balance and Prior Year-End Balance and Combine Tables
                      Zach DeMascole

                      Thanks Bryce,

                       

                      I tried to follow what you were saying, but I am a little confused with the Custom Date Picker and DATETRUNC.

                      Attached on Sheet 4 is an example of a chart that I would like to be able to select which month I am displaying.  For example, this is showing April 2018 as "Current Month", but what if I want to show it where February 2018 is "Current Month", and thus January 2018 is "Prior Month", etc.

                       

                      I added "zzz Most Recent Actuals" as a filter for the default and checked the box for it to update to the latest data.

                       

                      Thanks,

                      Zach

                      • 8. Re: Calculating Delta between Current Balance and Prior Year-End Balance and Combine Tables
                        Zach DeMascole

                        Thanks Zhouyi!

                         

                        In my real data set, I have several categories that are not part of "Inventory Category" or "DIO".  For example, I also have Cost of Goods Sold, Non-GAAP DIO, etc.

                         

                        Would this interfere with using the solution you outlined above?

                         

                        Thanks,

                        Zach

                        • 9. Re: Calculating Delta between Current Balance and Prior Year-End Balance and Combine Tables
                          Zhouyi Zhang

                          Hi, Zach

                           

                          Definitely you can. Drag the category field to filter and keep Inventory Category and DIO, and then add this filter to context.

                           

                          ZZ

                          1 of 1 people found this helpful
                          • 10. Re: Calculating Delta between Current Balance and Prior Year-End Balance and Combine Tables
                            Bryce Larsen

                            Hi Zach,

                            I've replicated your Sheet 4 using Parameters only.

                            It uses two parameters - one which you'd publish with "Most Recent" as Default as to always show the month with most recent "Actuals". But then you can flip it to "Custom" and use the value from the Date Picker. Please see images below!

                             

                            I've made the labels dynamic as well while also noting which month you're looking at.

                            Screen Shot 2018-05-22 at 9.59.56 PM.png

                            Screen Shot 2018-05-22 at 10.00.08 PM.png

                             

                            I've attached the workbook here for your reference. I think it helps as it avoids using some LOD expressions I think can be a bit unnecessary in this case. And it's overall easier to follow and understand. Happy to address any questions you might have!

                             

                            Best,

                            Bryce

                            1 of 1 people found this helpful
                            • 11. Re: Calculating Delta between Current Balance and Prior Year-End Balance and Combine Tables
                              Jennifer VonHagel

                              Hi Zach, sorry for the delayed reply, I have had a busy few days.

                               

                              You had asked about the difference between my and Zhouyi's calculations, since they seem to give the same result.

                              Jennifer and Zhouyi - Had a question about your responses.

                              In Jennifer's solution, she used "{FIXED [Inventory Category]......" whereas Zhouyi's solution did not have [Inventory Category] in the formula, but both solutions appear to have selected "Add to Context" for the Program1 filter.

                               

                              I'll show the difference with sample data. If you look here, we have these calcs:

                               

                              Quantity: Simply shows the sum at each line item in the table.

                              Fixed Region : Qty: Finds the sum at the level of Region.

                              Fixed: Qty: Finds the total sum across the whole data set.

                               

                              Notice that Region is NOT set to Context in the next couple screenshots.

                               

                              Now if I show the Region Filter and deselect one of them, the Fixed Region : Qty calc gives the correct Total, because it is broken into Totals by Region within the calculation itself. However the Fixed : Qty calculation is still showing the Total of all four regions - because it does not take Region into account in its calculation.

                               

                              BUT. If I now Add Region to Context in the Filter card, the Fixed : Qty calculation will take Region into account.

                               

                              So back to Zhouyi's and my calculations. He had { Fixed : calculation }, but the Inventory Category was added to context so it came out fine.  I had { Fixed Inventory Category : calculation } without the filter added to context and it gave the same result.

                               

                              Hope that makes sense,

                              Jennifer

                              1 of 1 people found this helpful
                              • 12. Re: Calculating Delta between Current Balance and Prior Year-End Balance and Combine Tables
                                Zach DeMascole

                                Bryce,

                                 

                                I really like what you did here.

                                A few follow-up questions:

                                 

                                1.  What is the easiest way to get what you did into my real workbook that wasn't built with sample data?  (Should I replicate all of your formulas/parameters into my real workbook?, Should I export your worksheet?, i.e.)

                                 

                                2.  Would it be possible to only have PA_DatePicker, but the default for this was always the most recent actuals?  If the user wants something else, then they adjust the PA_DatePicker.

                                 

                                3.  Is there a way to make the PA_DatePicker user selection to be the month and year?  For example, if users want to see February actuals, they choose February 2018 rather than a specific date in February?  Part of the reason I would want to do this is that our month-end actuals are usually not the last calendar day of the month.

                                 

                                Thanks a ton,

                                Zach

                                • 13. Re: Calculating Delta between Current Balance and Prior Year-End Balance and Combine Tables
                                  Bryce Larsen

                                  Hi Zach,

                                  Glad you liked it!

                                   

                                  Here are my responses:

                                  1. You should be able to identify all of the "new" calculated fields (and parameters) and simply copy and paste them provided the Dimensions/Measures it's referencing are the same as the ones you're looking to use.
                                  2. Unfortunately no. That's the advantage of the quick filter since 10.3 (I think) - default to "most recent". You can have PA_DateType floating on your dashboard. Then you can combine PA_DatePicker and a collapsible sheet that only shows when PA_DateType = "Custom". Then you can hide the Date Picker except when it's necessary. I know you can actually have it always be the most recent date...but requires using commandline stuff on the server that I'm not familiar with. Let me know if you need an example of the above, though!
                                  3. Well, unfortunately when allow "All" values in a DatePicker, you can't change the DisplayFormat (or how DatePicker is used). To address this you'll notice I actually truncate the dates of both the date picker and your date field as well.

                                            DATE(DATETRUNC("month", [PA_DatePicker]))

                                   

                                  Assuming you don't bring back different dates per month, this should work! Otherwise we can add an extra step to find max date per month and take that into consideration.

                                   

                                  Best,

                                  Bryce

                                  1 of 1 people found this helpful
                                  • 14. Re: Calculating Delta between Current Balance and Prior Year-End Balance and Combine Tables
                                    Zach DeMascole

                                    Hey Bryce,

                                     

                                    Maybe I'm a little slow for a Friday afternoon, but I have some follow-on questions haha:

                                     

                                    Point 2 above)  I think I follow what you're saying about not being able to combine the features of the "PA_DateType" and "PA_DatePicker".  To use your suggestion, would I use an action so that if "Custom" is selected from "PA_DateType" then a sheet pops up that houses "PA_DatePicker"?  If you have an example of what you are describing that would be extremely helpful.

                                     

                                    Point 3 above)  Which calculated field did you make in order to truncate the "Date1" field?  I cannot find it.   Are you saying that there is a way to make "PA_DatePicker" have a list of months/years?  My data should only have one date per month.

                                     

                                    New question (sorry)

                                    Is there a way to make the parameter selected for "Bryce's Sheet" also apply to other charts?  For example, can you utilize a parameter like a global filter?

                                    The reason I ask is that in my actual product for work, the dashboard consists of 7 of those charts you see on "Bryce's Sheet".

                                     

                                     

                                    Thanks again,

                                    Zach

                                    1 2 Previous Next