8 Replies Latest reply on Oct 24, 2017 4:19 AM by lenzy petty

    Sales for Highest Sales Year, for 1st Full Year, for Last Full Year, and Average and Total Sales for Date Range, on Bar Chart

    lenzy petty

      Use calculated fields to add Marks shapes on the bar graphs to show the following:

      1. Show Sales for the First "Full Fiscal Year"?

      2. Sales for the Last "Full Fiscal Year"?

      3. Sales for the Fiscal Year with the Highest Sales?

      4. Average annual Sales for the full Duration of the Fiscal Sales years of Sales?

      6. Total Sales for the full Duration of the Fiscal years Sales?

      Shinichiro Murakami

      Jonathan Drummey

       

        • 1. Re: Sales for Highest Sales Year, for 1st Full Year, for Last Full Year, and Average and Total Sales for Date Range, on Bar Chart
          Jonathan Drummey

          Hi,

           

          Since you tagged me I’ll respond to this, however I’m not going to give you answers. The screenshot you provided is clearly a copied Tableau view and where there is a list of questions (with #5 mysteriously not there) I’m guessing this is some sort of homework or examination question, and if I just provide you with the answers that’s not going to help you learn.

           

          I will give you a hint, you can get all the answers you need using table calculations or level of detail expressions.

           

          Jonathan

          • 2. Re: Sales for Highest Sales Year, for 1st Full Year, for Last Full Year, and Average and Total Sales for Date Range, on Bar Chart
            lenzy petty

            Hi Jeff,

            Posted a note for help on tableau community.   See response I got from Jonathan Drummey.  Is this the guy you recommended?

            1 of 1 people found this helpful
            • 3. Re: Sales for Highest Sales Year, for 1st Full Year, for Last Full Year, and Average and Total Sales for Date Range, on Bar Chart
              lenzy petty

              Hi  Jonathan,

              This is not a homework question.  I work for a a Fortune 100 company with sensitive and confidential data.  So, I mocked up the example to reflect what I am trying to achieve.  Jeff Black (part of the Tableau account Team that supports my company) suggested you as a resource for calculation related problems, so I mentioned you on the note.  I normally just mention Shin, because of his thorough insights.  This is not a homework problem, this is my job, and it is a real life business need that I am addressing.   The list of questions that I created, was created as a result of my interactions with Shinichiro Murakami, from whom I've learned tthe value of thoroughly articulating my requirements.  So I put considerable thought into building the requirements for my request, and into simulating those requirements in a way that would not compromise the sensitivity of my companies information.

              • 4. Re: Sales for Highest Sales Year, for 1st Full Year, for Last Full Year, and Average and Total Sales for Date Range, on Bar Chart
                Jonathan Drummey

                Hi,

                 

                I apologize for my mistaken assumption, given the clarification you provided I'd be glad to help. Please know that most everyone answering questions on the forums are volunteers so we answer when we can, and given the number of requests for help that I get I can be perhaps over-sensitive to requests that (to me) look like they are trying to take advantage of my time.

                 

                I have several questions to be addressed before I can give you complete answers, these are based on the workbook provided and the text you have provided so far.

                 

                1) In the provided worksheet the First Sale Date LOD and Last Sale Date LOD calculations are both being computed *before* the various dimension filters are applied. This leads to the first sale date for Europe/Ford/Fiesta to appear to be 2008 when the filtered MIN(YEAR([.First Sale Date])) would be 2015. Is the 2008 the value you want or should it be 2015?

                 

                2) Related to that, on the Gantt bar chart that is using the same ...LOD date fields that is also being laid out irrespective of the filter settings. Is that correct or should the Gantt bar marks respect the filter settings?

                 

                3) And also related to #1 and #2, should the calculations for the desired measures be based on the existing displayed results that are computed before the filters are applied or should they be computed after the filters have been applied?

                 

                4) Secondarily there's a potential for confusion with the Gantt chart since it is based on full years and FY so the marks are positioned on the FY boundaries. For example for the Europe/Ford/Fiesta the marks go from FY 2008 to FY 2022 so it looks like the car sales are from the beginning of FY 2008 to the beginning of FY2022. However the actual first sale date (given the present unfiltered LOD expressions per questions #1 and #2)  is 7/1/2008 i.e. the beginning of Q4 FY 2008 and the last sale date is 12/1/2021 i.e. two months into FY2022. Is that what is really desired here? Or does the view need to have FY as the tick marks while showing the data at a finer grain that would give a better sense of how much of each FY the car is available for sale?

                 

                5) If the answer to question #3 is that the existing view is what is really desired I have a sub-question: on the Gantt bars given the current layout presently each FY takes up some horizontal distance except the last FY which takes up 0 distance. Should that last FY really take up 0 distance or should it fill up the space for the FY and end just before the tick mark for the next fiscal year?

                 

                6) Given the existing Gantt bar marks the shape marks will be each centered on a given point. Should that point be the beginning of each FY, the end of each FY, or the middle? I ask this because I've worked with measures that are "in the year" so positioning them at the beginning or middle of the year works fine, and also worked with measures that are very specifically "end of year" that need placement at the close of each year.

                 

                7) All of the date fields in the view in the workbook are using or based on the .First Sale Date and .Last Sale Date fields where the .First Sale Date has default FY start of October, as do the two ...LOD fields. I'm guessing that the calculations for the measures that are desired are going to need to use the Order Date to determine what FY they fall into. a) Is that correct? b) Is October the correct beginning of the FY to use?

                 

                8) The Gantt bar is simply showing the duration of the sales. Measures #1-3 are requesting to show shapes with sales for the first fiscal year, last fiscal year, and sales for FY w/highest sales, however the shapes in the graphic are apparently just positioned at the first/last/highest years. How are the actual sales measure values to be displayed: In tooltips, mark labels, or something else?

                 

                9) A follow-up from #8 - what is to be done when values overlap, for example if the last fiscal year is also the year with the highest sales?

                 

                10) There is nothing I can see that specifies where Measures #4 and #6 should be displayed. Do these go in tooltips, in additional headers, or something else?

                 

                Regards,

                 

                Jonathan

                • 5. Re: Sales for Highest Sales Year, for 1st Full Year, for Last Full Year, and Average and Total Sales for Date Range, on Bar Chart
                  lenzy petty

                  Hi Jonathan,

                  Thanks for your consideration.  I have attached an updated Workbook with:

                   

                    1.  Sales$ for “First Sales Date lod” with Calculated field called

                    2.  Sales$ for “Last Sales Date lod” with Calculated field called

                    3.  Sales$ for “Lifetime Sales”, which is Total Sales$ from “First Sales Date lod” up to “Last Sales Date lod”

                    4.  “Average Sales$” for Lifetime of Sales

                   

                   

                  (1)    What I have not determined is the “First Full Fiscal Year” of Sales and the “Last Full Fiscal Year”, based on my October Fiscal Year Start Date.  None of my current calculation achieve this.  Currently, I only show the Total Sales for the first 12 Months of Sales, and the Total Sales for the Last 12 Months of Sales, as opposed to the first 12 months in a full Fiscal year that starts in October.

                   

                  (2)    Even my Average Sales$ does NOT capture the full number of months to compute the Average, instead is divides the Total Sales (First Sale through Last Sale) divided by the Number of Years, or Year part duration, from the First Sale to the Last Sale

                   

                  (3)    I also don’t know how to add Marks Shapes on the Bar Graph, to correspond with the Sales for the First Sales$ Amount at the beginning of the Bar, and the Sales Amount for the Last 12 Months on the End of the Bar Graph.

                   

                  (4)    Finally, I have not figured out how to (1)calculate and (2)add a Marks shape for the Sales Amount for the Year that has the Highest Sales, also known as the Peak Sales Year.

                   

                   

                  Below are my responses:

                   

                   

                    1.  In the provided worksheet the First Sale Date LOD and Last Sale Date LOD calculations are both being computed before the various dimension filters are applied. This leads to the first sale date for Europe/Ford/Fiesta to appear to be 2008 when the filtered MIN(YEAR()) would be 2015. Is the 2008 the value you want or should it be 2015?

                  YES, I want the 2008 Value – I am using LOD (First Sale Date lod) to identify the “First” Sales date of a (1)Car Version Code of a (2)Car Company in each (3)Region where the car is sold.

                  Same thing for the  “Last Sale Date lod”  Sale of a (1)Car Version Code of a (2)Car Company in each (3)Region where the car is sold.

                   

                  2) Related to that, on the Gantt bar chart that is using the same ...LOD date fields that is also being laid out irrespective of the filter settings. Is that correct or should the Gantt bar marks respect the filter settings?

                  Not sure I understand the question, but the Bar Chart needs to show the range of  (1)when the First Sale date occurs up to (2)when the Last Sale Date occurs.  If there is a better way to do this, I am open to it.

                   

                  3) And also related to #1 and #2, should the calculations for the desired measures be based on the existing displayed results that are computed before the filters are applied or should they be computed after the filters have been applied?

                  Calcs (First year of sales, Last year of sales, Highest year of sales, and average year of sales, should use the LoD Date calculations, but the Bar Graph View should align with Order Date.

                   

                   

                  4) Secondarily there's a potential for confusion with the Gantt chart since it is based on full years and FY so the marks are positioned on the FY boundaries. For example for the Europe/Ford/Fiesta the marks go from FY 2008 to FY 2022 so it looks like the car sales are from the beginning of FY 2008 to the beginning of FY2022. However the actual first sale date (given the present unfiltered LOD expressions per questions #1 and #2)  is 7/1/2008 i.e. the beginning of Q4 FY 2008 and the last sale date is 12/1/2021 i.e. two months into FY2022. Is that what is really desired here? Or does the view need to have FY as the tick marks while showing the data at a finer grain that would give a better sense of how much of each FY the car is available for sale?

                  Yes,  the view should have FY as the tick marks while showing the data at a finer grain that would give a better sense of how much of each FY the car is available for sale

                   

                   

                  5) If the answer to question #3 is that the existing view is what is really desired I have a sub-question: on the Gantt bars given the current layout presently each FY takes up some horizontal distance except the last FY which takes up 0 distance. Should that last FY really take up 0 distance or should it fill up the space for the FY and end just before the tick mark for the next fiscal year?

                  Since the FY Dates are based on Exact Dates, I’d say that the FY does not have to take up the full distance.

                   

                  6) Given the existing Gantt bar marks the shape marks will be each centered on a given point. Should that point be the beginning of each FY, the end of each FY, or the middle? I ask this because I've worked with measures that are "in the year" so positioning them at the beginning or middle of the year works fine, and also worked with measures that are very specifically "end of year" that need placement at the close of each year.

                  I don’t have a particular preference, so I will say the middle.

                   

                  7) All of the date fields in the view in the workbook are using or based on the .First Sale Date and .Last Sale Date fields where the .First Sale Date has default FY start of October, as do the two ...LOD fields. I'm guessing that the calculations for the measures that are desired are going to need to use the Order Date to determine what FY they fall into.

                  a) Is that correct? YES      b) Is October the correct beginning of the FY to use?  YES

                   

                  8) The Gantt bar is simply showing the duration of the sales. Measures #1-3 are requesting to show shapes with sales for the first fiscal year, last fiscal year, and sales for FY w/highest sales, however the shapes in the graphic are apparently just positioned at the first/last/highest years. How are the actual sales measure values to be displayed: In tooltips, mark labels, or something else?

                  I would like Marks Shapes and Labels for the first fiscal year sales amount, last fiscal year sales amount, and highest sales amount to be located at the corresponding date position on the bar

                   

                  9) A follow-up from #8 - what is to be done when values overlap, for example if the last fiscal year is also the year with the highest sales?

                  I don’t know best practices for dealing with overlaps, as I have yet to see a good solution, so any recommendation would be appreciated, otherwise, I can deal with them manually.

                   

                  10) There is nothing I can see that specifies where Measures #4 and #6 should be displayed. Do these go in tooltips, in additional headers, or something else?

                  Average Annual Sales amount, and Total lifetime sales amount can go in headers.

                   

                  In Summary

                   

                   

                    1.  What I have not determined is the “First Full Fiscal Year” of Sales and the “Last Full Fiscal Year”, based on my October Fiscal Year Start Date.  None of my current calculation achieve this.  Currently, I only show the Total Sales for the first 12 Months of Sales, and the Total Sales for the Last 12 Months of Sales, as opposed to the first 12 months in a full Fiscal year that starts in October.

                    2.  Even my Average Sales$ does NOT capture the full number of months to compute the Average, instead is divides the Total Sales (First Sale through Last Sale) divided by the Number or range of Years from the First Sale to the Last Sale

                    3.  I also don’t know how to add Marks Shapes on the Bar Graph, to correspond with the Sales for the First Sales$ Amount at the beginning of the Bar, and the Sales Amount for the Last 12 Months on the End of the Bar Graph, and the Highest (Peak) Sales Amount to correspond to the year it occurs on the bar.

                  • 6. Re: Sales for Highest Sales Year, for 1st Full Year, for Last Full Year, and Average and Total Sales for Date Range, on Bar Chart
                    Jonathan Drummey

                    Hi, thanks for the answers! One more question:

                     

                    There are order dates for several cars that are outside the first & last sale dates. What should be done about that?

                     

                    Jonathan

                    • 8. Re: Sales for Highest Sales Year, for 1st Full Year, for Last Full Year, and Average and Total Sales for Date Range, on Bar Chart
                      lenzy petty

                      Hi Jonathan,

                      Yes, I got it answered….made a Tableau Doctor appointment at the conference in Las Vegas.   Thanks for asking.  I will eventually post the solution, but the explanation of the solution is a bit lengthy, (as was my request).