7 Replies Latest reply on Apr 26, 2016 12:18 AM by Rajeev Pandey

    (Urgent )How to capture starting date and ending date of the Date Range Slider

    Rajeev Pandey

      Dear Tableau Gurus,

       

      Could you please help me with your tableau expertise as I am not able to perform the below operation.

       

      Requirement : I have created three sheets .Sheet 1 contains categories wise sales data from 1st Jan 2013- 4 March 2013 and this date range is in the form of Date Range Slider . In the same Way I have created  another Sheet which contains the same data from 1st Jan 2014- 4 March 2014 .Here also the date range is in Slider .Sheet 3 would contain the data in such way like (Sum (Sales) from 1st Jan 2013- 4 March 2013 / Sum (Sales) from 1st Jan 2014- 4 March 2014). All these three sheets I need to consolidate in Dashboard so that when user Select Date range Filter for sheet 1 and Date Range Filter to sheet 2 ,the required Calculation should Happen in Sheet 3.

       

      Another Confusion:

      I am also Confused what if there is no value for 2013 Date Range Silder for one of the category and the same category is not having the value when user selected the Date Range Filter 2014.So at that time Division will give the wrong Result because if there is no value in that particular category for 2013 then that category will not appear in the now of Rows , it should automatically filtered out which would result lest no of Rows for 2013 filter in comparison to 2014 Filter. So in that case if there is not value for any category it should show 0(zero) so that no of rows remain same.

      I did everything as per my knowledge but no luck ,this requirement is eating my brain ,spent so many hours in figuring out but no success yet.Please help me

       

      My user want to see the date in DateSlider Range and I dont know how to capture the data between these two dates like the starting date and the ending date of Order Date Slider

      Expected Output:

      Pooja Gandhi Norbert Maijoor Shinichiro Murakami Rody Zakovich Simon Runc Jonathan Drummey Shawn Wallwork

        • 1. Re: (Urgent )How to capture starting date and ending date of the Date Range Slider
          Dave Hart

          Hi Rajeev,

           

          I think that you are trying to combine quick filters from the same datasource which can cause a few problems. You could use parameters of course, but then you wouldn't get your nice date range slider.

           

          One way you could solve it is with blending: by having your date filters in secondary datasources over a base datasource that contained the categories.

          I've attached an example. the reason that you have to set up a base datasource is that you need to have the primary datasource being the same on all the sheets to allow the secondary datasource quick filters to be applied across sheets

          (you wouldn't have to do it in multiple sheets of course, you could just have a single sheet with the base datasource and the duplicated ones as filters, but I'm trying to follow your example)

           

           

          To do this, you need to duplicate the datasources, so it may not be a perfect solution, but if your datasources are over a database it should be fine.

           

          Hope this helps

           

          Dave Hart

          InterWorks

          2 of 2 people found this helpful
          • 2. Re: (Urgent )How to capture starting date and ending date of the Date Range Slider
            Rajeev Pandey

            Thank you so much Dave , this is really Helpful but I have two quick Question.

            1)Is there any way we can capture the Starting and Ending date of Date Range Slider so that same date i can utilize in my calculation.

            2) What if the category  value is not present in the sheet 1 for the Given date Range and the same Field is available in sheet 2 for the same date range of another Year.At that time my Division calculation will go wrong . Shall I Wrap the sheet 1 Sales value into Zn . Will that Help. How to tackle this Problem

             

            Any suggestion/workaround is greatly appreciated

            • 3. Re: (Urgent )How to capture starting date and ending date of the Date Range Slider
              Rajeev Pandey

              Thank you so much Dave , this is really Helpful but still I am not able to achieve 100 % success in my case.

              As you can see in my below image, When I applied the Filter range for march 1,2015 - March 31,2015 . It is giving me 6 Rows of Values

              But if you can see in the below image where the Date Filter Range is from march 1,2016 - March 31,2016.I am only getting the 4 Rows only . Shared Services and Digital and Mobile Products are not present for this Range as well.

              SO when I m putting the Values and creating the common sheet , I am missing values for these two Values which I wanted to see in my report as well . So if there is no value with the data it just show that Row and will Present the Zero value.

              As you can see , I am getting only 4 rows and missing the Value for Shared Services and Digital and Mobile Products

              because that value is not present in my 2016 date range

              Please help me in getting this issue resolved.

              Jonathan Drummey Sir,would you also throw some light if possible what I m missing here

              • 4. Re: (Urgent )How to capture starting date and ending date of the Date Range Slider
                Dave Hart

                Hi,

                 

                1. if you want the start and end date of the date range slider then you can just call MAX(secondarydatasourcename.datefield) or MIN(secondarydatasourcename.datefield).

                2. if the category isn't present in either of the datasources that are being filtered, they will still remain in the base "primary" dataset, so you'll still get a row for them, but the value will return to NULL. then you need to decide what NULL means. if you want NULL to mean zero then, yes, you should use ZN. You may just want to leave it NULL though to avoid any divide by zero errors.

                 

                hope this helps

                Dave

                InterWorks

                • 5. Re: (Urgent )How to capture starting date and ending date of the Date Range Slider
                  Dave Hart

                  Hi Rajeev,

                   

                  I'm not sure you're going to get exactly what you want here, as I don't think there's a way for tableau to do this whilst retaining the quick filter control, certainly not without venturing too far down the "hack" route (we're already a bit down that route anyway...). Using blending you can answer the question, but it will filter rows out as Tableau doesn't allow "Show Empty Rows" when a blend is involved.

                   

                  If you wanted to go one further step down that path, you remove the subcategory from the blend and just blend it on a constant field then you will be able to find the filters start and end date using MAX and MIN as above. you could then use that in a calculation, BUT you couldn't use it in a row-level calculation (like the one you presented in the original post), it would have to be an aggregated one, so you'd have to do the primary query at the lowest level and then do everything in a table calculation at the end. whilst you might get the result you were looking for, it would definitely not perform very well and would be difficult to maintain.

                   

                  If you wanted to stick to the type of calculations in your original post, I'd probably consider the parameter option: have four parameters, 2013 Start, 2013 End, 2014 Start, 2014 End, then you could use them in row level calculations. You'd get four independent sliders rather than two range sliders, but it would answer your business case and work around some of tableau's default behaviours that you're trying to avoid.

                   

                  Maybe others will be able to offer a different way, or maybe Tableau v10 may offer new options.

                   

                  Dave

                  • 6. Re: (Urgent )How to capture starting date and ending date of the Date Range Slider
                    Jonathan Drummey

                    Hi Rajeev,

                     

                    I don't have time to go into this in detail, reading through what you wrote it looks like you are expecting filters on secondary sources to only filter those sources. That is not how Tableau works, filters apply to the source and if that changes what is returned in the data blend then the view is filtered as well.

                     

                    The 4 parameter approach that Dave suggested is one of two ways to set up independent filters. The other way is to set up a cross product of your data so there are enough extra rows to filter out what you don't need. Given the number of dates involved I'm guessing that won't be an option, so the 4 parameter approach would be the way to go.

                     

                    Jonathan

                    1 of 1 people found this helpful
                    • 7. Re: (Urgent )How to capture starting date and ending date of the Date Range Slider
                      Rajeev Pandey

                      @Dave Hart

                      If you wanted to go one further step down that path, you remove the subcategory from the blend and just blend it on a constant field then you will be able to find the filters start and end date using MAX and MIN as above. you could then use that in a calculation, BUT you couldn't use it in a row-level calculation (like the one you presented in the original post), it would have to be an aggregated one, so you'd have to do the primary query at the lowest level and then do everything in a table calculation at the end. whilst you might get the result you were looking for, it would definitely not perform very well and would be difficult to maintain.

                      Sir could you please help me and provide a working example what you meant above. How to perform the Above calculation. A detailed example would be highly appreciated.