1 2 Previous Next 20 Replies Latest reply on Jun 18, 2018 10:28 AM by Zach DeMascole Go to original post
      • 15. Re: Calculating Delta between Current Balance and Prior Year-End Balance and Combine Tables
        Zach DeMascole

        Thank you Jennifer!

         

        This was a great explanation and makes perfect sense.

         

        Happy Memorial Day Weekend!

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

          Hi Zach DeMascole

          See attached for example of hiding the date picker. It's just a sheet that expands to push out the date picker only when Date Type is custom.

           

          I've put all my calculated fields in folders. I called it CF_DateMonth (creative, I know! Heh.). Unfortunately cannot show that DatePicker as such - only truncate it to match CF_DateMonth. You could look to add a signifier elsewhere to make it clear what it's doing if desired. I usually just have it in the title.

           

          Yes. You can add in the same filter as a Data Source Filter. But make sure you change all the calcs on other pages as well. I've redone the other sheets with new measures in the attached workbook. Of course, you can/should rename these to something more friendly.

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

            Hey Bryce,

             

            Thanks again!  This is looking great.  The transition to the "CF_..." calculations is really helping me simplify some other things.

             

            Two questions:

             

            1.  I followed the same steps you used on the "Center Table - Balances" sheet, but did not get the same result.

                 Does it look like I'm missing something?

             

            Screeenshot.PNG

             

             

             

            2.  On my version of "Dashboard 1" (which now has 6 of those bar graphs), when I change the timeframe using the date picker, it changes the order of the bars on the bar graph.  But, on your "Dashboard 1", the months always keep the same order.  Do you know what the trick is there?

            For example, on mine if I pick a date in like March, "YE 17 Actual" stays at the top, but is then followed by "YE 18 Forecast", then "Current Month", then "Last Month".  I would really like to keep them in the order of "YE 17 Actual", "Last Month", "Current Month", "YE 18 Forecast" as you have managed to do.

             

            Thanks!

            Zach

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

              Hey Bryce Larsen!

               

              I took my workbook with actual data and applied the sample data to it so I can show you what I'm running into, as described in my last reply.

              You will see there are several calculated fields with errors (those are formulas I used previously but didn't have a chance to clean up yet).

               

              If you go to the dashboard titled "New6PackwithCondFormatting" you can see what I've been trying to create.

              It has come a LONGGGG WAY with all of your help.

               

              Dashboard "New6PackwithCondFormatting" illustrates the issue I am having with the Center Tables and how the order of the bars/ colors of the bars changes when I utilize the Custom Date Selector.

               

              Also - The approach you setup for calculating delta (formula "CF_MonthVsLYEnd_Actuals") is really helpful and I tried using it for the "YTD Delta" and "Prior Delta" boxes next to each category, but it seemed to be subtracting the Prior Year End Actuals from 0.  I tried addressing it by using "ZN()" but it just made the cells come up blank.  Maybe something similar is happening with my center table?  It's odd that your sample workbook didn't have any issues, but when I replicated it in my actual workbook AND my sample data workbook it came back blank in both.  The "YTD Delta" and "Prior Delta" on the dashboard do not adjust based on the "PA_DatePicker" or "PA_DateType" parameters, but I think I can fix that by using the same approach for the center table once I understand what I'm doing wrong there.

              Any advice you can give is super super helpful.

               

              Thanks,

              Zach

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

                Hi Zach DeMascole

                Sorry for delay. Work-work got busy, away for a wedding, etc. etc. Heh.

                I think part of the issue may have been my doing in that I was changing how things were calculated. Worrying that the final date in each year might not actually be 12/31 I decided to change to truncate everything to start of the month. However, I see this wasn't done for CF_Date_YearLast and CF_Date_YearThis. Simply changing the MAKEDATE function in these for be the 1st instead of the 31st solved the issue.

                 

                This also required changing the CF_DateLabel and CF_DateLabelCD function so that it used CF_DateMonth for final two lines of the IF-THEN statement.

                 

                A couple additional edits I would make:

                • I would also suggest you ensure you're using CF_DateLabelCD on the color pane rather than the DateLabel itself. Otherwise the color of the bars change when you change months, which I imagine you don't really want and would rather it consistently be gray, gray, blue, green
                • Similarly, rather than filtering on CF_DateLabel having certain values, I would simply exclude NULL to take care of future dates coming in (and possibly use CF_DateLabelCD as it might perform more quickly). I've done this for the Frames for an example
                • Lastly, sort CF_DateLabel on CF_DateLabelCD rather than alphabetically or (as it seems currently) manually sorting. Against this would cause issues when changing months. You'll notice both of these when you change from Most Recent to Custom date. Modified this in Frames sheet as well. Now the order of the measures never change.

                   

                • Another consideration: fixing your dashboard size as tableau has to read in the user's screen resolution to change this every time. It might be quicker if it's always at a fixed size (like letter landscape) and ensure every user views it the same.

                 

                One other edit I made was to the Central Table. I kept the title on the DIO sheet, but I simply made it a space of same size as the adjacent Gross Inventory title. Likewise, I added a blank header of "" on the Gross Inventory sheet. This allowed me to easily line up the rows between the tables.

                 

                Lastly, I made it so the Custom Date picker hides unless you select Custom from the Date Type.

                 

                See if this helps address most issues.

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

                  Hey Bryce Larsen,

                   

                  This was an awesome help!  Been meaning to get back to you!

                   

                  I think I'm just about ready to put the bow on this project. I couldn't have done it without you.  This ask from my manager certainly pushed me out of my Tableau comfort zone, but the team is very happy with this dashboard and it is replacing a lot of manual work.

                   

                  Two minor (final) things that I could really use your help on:

                  1) The feature you added to hide the custom date picker when "Most Recent" is selected for Date Type is great. But I am having some difficulty replicating that with my actual dashboard with real data.  I see that you used the sheet titled Blank Custom and made it tiled on the dashboard, but I don't understand how it works.  On Blank Custom, I can't tell what changes based on what is selected for the Date Type.

                   

                  2)  A cosmetic thing (but apparently important to my manager) - Is there a way that I can add a border line on the left side of the Frames, Interiors, Structural, Tires sections to make it more clear that the YTD delta and Prior delta are applicable to the graph/section to the left?  I have attached an example of what I am envisioning.  I imagine it has something to do with baseline borders on the graphs, but I tried several options and couldn't get it to work.

                   

                  Any idea on either of these would be super helpful.

                   

                  Thanks again for everything,

                  Zach

                  1 2 Previous Next