10 Replies Latest reply on Jun 9, 2018 9:35 AM by Jim Dehner

    Lining up Seasonal Variability

    Garrett Joeckel

      Hello,

       

      I work in the produce business and commodity start dates shift around from year to year.  So a start date on cherries may be May 28 last year, and June 8 this year.  My goal is to line up to seasons with a DAY 1, DAY 2, etc. calculation, where DAY 1 will be the first day of shipping for each corresponding year.  Currently this is how the data looks that shows the variability for the years, the days are based on calendar years.

       

      Again my goal is to line the data up so it looks like this (did this manually in excel):

       

       

      The package workbook is attached, any help would be greatly appreciated!!

       

      Thanks,

      Garrett

        • 1. Re: Lining up Seasonal Variability
          Jim Dehner

          Hi

          see the attached

           

          is this what you wanted

           

           

          if so here are the formulae

          all the table calcs are set like this

           

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Lining up Seasonal Variability
            Garrett Joeckel

            Hi Jim,

             

            Thanks for taking a stab at it.  I think the formulae are all functioning properly, but there are multiple months involved, so the DAY(Ship Date) on the Rows shelf is aggregating to the day level for all months, typically a cherry season is around 70-80 days, so I'm trying to show day 1 - day 80 or whatever the last day # of the longest season is.  But it does look like the 1st date of each season is showing up under day 1, except for 2018 for some reason that is showing up under day 2, something to do with how the table calc functions?

             

            Thanks,

            Garrett

            • 3. Re: Lining up Seasonal Variability
              Jim Dehner

              Ok - lets try this one -

               

               

              it will go out past the month

               

               

              here are the new formula - start date by year

              running using the start date by year

               

              start with a viz like this

               

               

              the running date and index are set like this

               

              note NDY of ship date is the lowest level but it is not on rows or columns - but it is in the detail frame

               

              Jim

               

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              • 4. Re: Lining up Seasonal Variability
                Garrett Joeckel

                Jim

                 

                Yes this is perfect, exactly what I was looking for, thank you!

                 

                Garrett

                • 5. Re: Lining up Seasonal Variability
                  Jim Dehner

                  Thanks - sorry I didn't get the first time -

                  Glad to help out

                  Jim

                  • 6. Re: Lining up Seasonal Variability
                    Garrett Joeckel

                    Hi Jim,

                     

                    I've implemented solution into my workbook and its working perfectly.  Now I've thought of another level of functionality I'd like to add as a LOD calc to the top of the viz.  I'd like to show the season to date average price, so say it is now day 10, I'd like to see the average price for the first 10 days of each season and need it to be dynamic based on the current day of the season.  I've got the calc to work for the entire year easily enough but I'm not great with the date calculations.  This has been a good learning experience looking at how you are making this work.

                     

                    Thanks,

                    Garrett

                    • 7. Re: Lining up Seasonal Variability
                      Jim Dehner

                      Hi First a question - your are saying that if the day is day 10 you want the sum of days 1-10

                      -  are you saying you want to determine the day based on Today() the date function and use the number of days for each year in the viz -

                      the first 10 days of  2014, 2015, 2016,2017, 2018 ?

                      Jim

                      • 8. Re: Lining up Seasonal Variability
                        Garrett Joeckel

                        Yes that's correct.

                         

                        So I would have a season to date average for each year for the first 10 days.  Then on day 11 it would be the first eleven days, changing dynamically as we progress through the season based on the current day.

                         

                        Thanks,

                        Garrett

                        • 9. Re: Lining up Seasonal Variability
                          Jim Dehner

                          OK - going to take a little thinking - can't just drop an LOD  in here - we are using table calculations to get the date alignment

                          • 10. Re: Lining up Seasonal Variability
                            Jim Dehner

                            see the attached

                            this is not exactly what you envisioned but it will provide the information

                            As I posted before - the viz uses index() to determine the running date and the number of days into the season -

                            Index() is a table calculation that is calculated art the bottom of the order of operations - LODs are all calculated well above the table calculations

                            Image result for tableau order of operations

                             

                            the attached returns this

                            -

                             

                            The viz will update tomorrow to add another day to the table - I haven't figured a way to get just a single record by year for the season to day

                            Only the column repeating the value you see

                            here are the formulae

                             

                            you have seen the first 2 before

                             

                             

                            set like this

                            This determines the number of days the today is in the season - need it to determine how many days to sum in each year

                            note dates are different in each year so you need a fixed scalar number od days

                             

                            This gets the daily sum of price (not sure that is what you really want - may be sales?)

                            set like this

                            This totals the sales for the season to date by year

                            the total is a nested table calc - the deepest level is running date set as shown - but the outer level is set differently see below

                             

                            this limits the chart to the correct number of days - it increments each day

                             

                            You need this filter to force the running date to be calculated out of order

                             

                             

                            create the viz

                            Index is set as shown

                             

                            hope you will be able to use the solution

                            Jim

                            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.