13 Replies Latest reply on Feb 8, 2012 2:00 PM by Shawn Wallwork

    Filtering dates by Criteria

      Hello,

       

      I have built a booking curve system which I am trying to filter weeks of sale based on season selected.

       

      For the Winter season, the weeks of sale which affect Winter are all up to 4/22/12 (I only have week data....4/15, 4/22, 4/29, etc).

       

      However for Summer the weeks of sale which affect Summer are all up to 10/27/12.

       

      So when the user selects Winter I do not want any information from weeks greater then 4/22/12 to be graphed.  I have tried to used the following (and variations of it) to no avail: 

       

      CASE [SAICON] WHEN "Winter" THEN ([SEMVEN09]) < 22-April-12 Else [SEMVEN09] End

       

      However when the user selects Summer I am fine with all of the available weeks being displayed and graphed.

       

      I was also trying to figure out if there was a way to use the condition filtering but was having trouble as well.

       

      Any help would be apprecaited.

      Thanks!

       

      Mike

        • 1. Re: Filtering dates by Criteria
          Shawn Wallwork

          Is this what you're looking for?

          • 2. Re: Filtering dates by Criteria

            Hi Shawn,

             

            Thanks for help but I'm getting the error that I cannot open tihs file as it was created in newer version of Tableau.  Mine is version 6000.10.1214.1349

             

            Since I'm new to the product do you have any suggestions as to how to open it?

             

            Thanks,

             

            Mike

            • 3. Re: Filtering dates by Criteria
              Shawn Wallwork

              If you're new to the product then this should have come with free upgrades and support for a year. So you'll definitely want to upgrade to 7.0 Contact support and they'll help you with account information and getting to the download page. In the meantime all you have to do is create a parameter (oops I meant calculated field) like this:

              WinterSummer.PNG

              The Select Season: interactor is just a quick filter that I edited the True/False to a Summer/Winter aliases. I think everything else is pretty straight forward. Hope you're able to get it to work. BTW the two seasons are mutually exclusive. You said: "However when the user selects Summer I am fine with all of the available weeks being displayed and graphed."

              I'm hoping you're also fine only showing the summer dates.

               

              --Shawn

               

              Message was edited by: Shawn Wallwork

              • 4. Re: Filtering dates by Criteria

                Hi Shawn,

                 

                This almost gives me what I want (and I'm sure that the reason it doesn't work is on my part).

                 

                I originally had a selection for either Summer or Winter and the user selected their desired dates.  Up until this point I had pervious Summers and the current Winter - which ends 4/22/2012.  Now when I add the upcoming Summer - I have STLY data which maps the data sold in 2011 against the corresponding date in 2012 (example:  the week of 7/17/2011 is mapped against 7/15/2012).  This causes the Winter graph to show sales that are past the end of April and thus only relevant to the Summer season...so the graph sorta trails off into an irrelevant line.  However I can't exclude these dates from my chart as I'm using the same chart and just switching seasons.

                 

                When I use your solution and I select TRUE in this case it just gives me all the weeks of sale up to April 22 but I'm unable to differentiate between seasons at this point. 

                 

                However I wanted it to be more of a conditional statement, such as (and I'm writing it in English as opposed to Tableau language)

                 

                If user selects Winter - end weeks of sale for Winter at 4/22/2012

                If user selecst Summer - end weeks of sale for Summer at 10/27/2012

                 

                I could do this just fine in Excel but I'm struggling with it in Tableau.

                 

                Sorry if I'm going around in circles a bit on you.

                 

                Mike

                • 5. Re: Filtering dates by Criteria
                  Shawn Wallwork

                  Hi Mike if you post a packaged workbook with sample/mocked up data (if that's an issue) I'll take a look.

                   

                  --Shawn

                  • 6. Re: Filtering dates by Criteria

                    Hey Shanw - I'm trying to do that but can't seem to figure out how ......I can email it to you if that is easier but I don't have the option to attach anything but a Viz Link or a Hyper Link. 

                     

                    Which option in the forum allows you to post a book?

                    • 7. Re: Filtering dates by Criteria
                      Shawn Wallwork

                      Michael, yeah this is one of the less than perfect new 'features' of the forums. When you open the reply box look for:

                       

                      Reply.PNG

                      in the upper right hand corner. This will open an expanded window. The attach file is at the bottom. BTW, Tableau I really do appreciate all the hard work you guys put into the new forums Really!

                       

                      --Shawn

                      • 8. Re: Filtering dates by Criteria

                        Here you go Shawn - thanks for your help.

                        • 9. Re: Filtering dates by Criteria
                          Shawn Wallwork

                          Michael, here's what you need to do:

                           

                          1. Clear the previous 'Select Season' filter

                          2. Create a parameter 'Select Season:' set up like this:

                           

                          MaxDate-1.PNG

                          3. Then create a calculated field called 'Max Date' like this:

                          MaxDate-2.PNG

                          4. Create another calculated field called 'Filter on Max Date' like this:

                          MaxDate-3.PNG

                          5. Put this last one on the filter shelf (pick either value as default).

                          6. Show the parameter card and make it single value. (It should show 'Winter' & 'Summer'.)

                           

                          Hope this works for you.

                           

                          --Shawn

                          • 10. Re: Filtering dates by Criteria

                            Hey Shawn,

                             

                            First I imported my new data so the Summer 12 information is present. 

                             

                            I did as you instructed and the TRUE works for Winter but the FALSE doesn't do anything for me.


                            What I am trying to do is to prevent the Winter from showing the weeks in May, June, July, etc which are not pertinent to that season.

                             

                            I've attached my book.

                             

                            Mike

                            • 11. Re: Filtering dates by Criteria
                              Shawn Wallwork

                              Michael, first you need to use the parameter 'Select Season' NOT the quick filter 'Filter on Max Date' (See step 6 above.) Is this what you want? Or...

                               

                              Second please clarify: "What I am trying to do is to prevent the Winter from showing the weeks in May, June, July, etc which are not pertinent to that season." Do you mean you don't want to see these months for all three years (2010-12)?

                               

                              --Shawn

                              • 12. Re: Filtering dates by Criteria
                                Shawn Wallwork

                                Michael are these the two results you are looking for?

                                 

                                Dates1.PNG

                                 

                                Dates2.PNG

                                 

                                --Shawn

                                • 13. Re: Filtering dates by Criteria
                                  Shawn Wallwork

                                  OK, as best I can figure this will give you the result you are looking for. Copy and paste it into your calculated field:

                                   

                                  IIF([Select Season]=TRUE,

                                  DATE('1/1/2012' )<[SEMVEN09] AND [SEMVEN09]<DATE('4/22/2012' ) OR    //Winter set

                                  DATE('1/1/2011' )<[SEMVEN09] AND [SEMVEN09]<DATE('4/22/2011' ) OR

                                  DATE('1/1/2010' )<[SEMVEN09] AND [SEMVEN09]<DATE('4/22/2010' )

                                  ,

                                  DATE('1/1/2012' )<[SEMVEN09] AND [SEMVEN09]<DATE('10/27/2012' ) OR    //Summer set

                                  DATE('1/1/2011' )<[SEMVEN09] AND [SEMVEN09]<DATE('10/27/2011' ) OR

                                  DATE('1/1/2010' )<[SEMVEN09] AND [SEMVEN09]<DATE('10/27/2010' )

                                  )

                                   

                                  And it should give you this result:

                                   

                                  Dates1.PNG

                                  Dates2.PNG