5 Replies Latest reply on Jan 15, 2019 12:41 PM by S Shah

    Interactive date change for Holiday data

    S Shah

      Hey everyone,

      I have tried to search the community and google for a solution. Here is the situation.

      I have daily sales data for several years and I show best Monday / Tue... regular days and then holidays etc. to highlight which year each of the days were better. As an example, following image shows all new year days sorted by sales. Obviously for Sunday - Saturday, I am picking only top 10, thus label.

       

       

      Problem Statement :

      The issue is that client wants to see day after some of the holidays as well to see how the trend had been.. i.e. I have to add drop down of  0 to 4 days, and when I select 2, I have to show sales for 2 days after "New Year" . i.e. in above image, it will be 01/03 for all years.. Obviously, data needs to be sorted accordingly to sales.

      But I can't seem to find solution to do that. I know it's matter of DATEADD function. So I created

      DATEADD ( 'day', [PickDaysToAdd], [SalesDate] ), where [PickDaysToAdd] is parameter control with 0 to 4.

      When I use the parameter, it shifts the dates, but the sales amount do not change and it kind of wipes out one entry from the bottom...

       

      in simple terms, I have to move the record pointers to next days... and shows those counts..

       

      Has anyone faced similar use case ? .. basically, I have to allow user to pick # of day after Holiday to view the trend..

      In case of other holidays, I have data set marked exactly which date is a particular holiday.. So that's covered..

       

      I will work to create sample worksheet. But data being proprietary, I have to explore ...

      Shah

        • 1. Re: Interactive date change for Holiday data
          Rahul Sharma

          Hi Shah,

           

          Refer below video I have created for the solution. For sake of simplicity, I have created data set only for the first 15 days of Jan. In case you want the sum of sales amount for the selected days, change the logic in calculated field formula.

          Interactive date change with parameter - YouTube

           

          If this helps, please the answer as correct so that the thread can be closed.

           

          Cheers,

          Rahul

          • 2. Re: Interactive date change for Holiday data
            S Shah

            Hey Rahul

            I can't open the file.. I have 10.5.

            I will try to convert and open it.

            • 3. Re: Interactive date change for Holiday data
              Rahul Sharma

              Hi Shah,

               

              You can check the video I have shared all the sets in there.

               

              Cheers,

              Rahul

              • 4. Re: Interactive date change for Holiday data
                S Shah

                Ok.. I have managed to create subset of data and workbook. I have attached the workbook here.

                Unfortunately, I can't create the video to post it from work environment, but the idea is as below.

                First dropdown has normal "Sunday thru Saturday, plus public holidays. In case of Holidays, we have to give users ability to see upto 4 days view on each year's sales. i.e. in the screenshot below, if I were to pick +2 in second drop down, all my dates shown below would go up by 2... so 1/15/18 becomes 1/17/18, 1/21/13 becomes 1/23/13.. This way, users can see how much was lift we had days after holidays...

                 

                The first dropdown works perfect in normal operation. I need to add the condition where first I have to add days to currently filtered view and then pull those records and sort it.

                 

                Hope the attachment makes it easier to understand.
                BTW, many thanks Rahul for your post. I watched the video. You have great feature, but you are showing sequential dates, in my case it can be random.

                 

                Shah

                snehal.r.shah@verizon.com

                • 5. Re: Interactive date change for Holiday data
                  S Shah

                  Hey friends,

                  wanted to update you guys.

                  So I solved it with little bit twist on dataside as well. I modified data to pair with my logic from Tableau side so that it can work.

                  I have updated the code field to include word "REGXXX#" where XXX is holiday type and # is 0 to 4 and I inserted regular rows plus 4 more rows for that data.

                  Then I updated calculated field to use logic accordingly.

                  There was an issue still with "Thanksgiving" and "BlackFriday" , so removed all BlackFriday entries and using only ThanksGiving.

                   

                  Let me know if anyone needs calculated field. I don't know if this is universal problem people face or not, but at least it was a good challenge for me.

                  Thank you for your support.

                  Shah