12 Replies Latest reply on Feb 25, 2016 8:53 PM by Amanjot Klair

    Date Calculation

    Rahul Panday

      Hi All ,

       

      I have one requirement, if I select Jan form moth filter then view should have data  for dec ..means view should show previous month of data  from selected months( excluded) .

      I have month in string formate not in date formate & I am bot keeping month in view ..it will be in filter  only .

       

       

       

      Please anyone provide input ..Thank you

        • 1. Re: Date Calculation
          Vasily Lavrov

          Just "rename" your months to "months+1"))
          If your [Original Month] in string format, you can create new field [Filter Month] based on [Original Month] this way:

           

          CASE [Original Month]

          WHEN "Jan" THEN "Feb"

          WHEN "Feb" THEN "Mar"

          WHEN "Mar" THEN "Apr"

          WHEN "Apr" THEN "May"

          WHEN "May" THEN "Jun"

          WHEN "Jun" THEN "Jul"

          WHEN "Jul" THEN "Aug"

          WHEN "Aug" THEN "Sep"

          WHEN "Sep" THEN "Oct"

          WHEN "Oct" THEN "Nov"

          WHEN "Nov" THEN "Dec"

          WHEN "Dec" THEN "Jan"

          END

           

          Put [Filter Month] to filter and [Original month] to view.

          So when you choose "Jan" in filter you will get "Dec" for view.

           

          I hope I correctly understood you))

          1 of 1 people found this helpful
          • 2. Re: Date Calculation
            Rahul Panday

            Hi Vasily ,

             

            I have used your technique showing all null ..see I do not have to  keep months in view , the only thing that if I select Jan then view should

            show data for dec , if I select march then view should show data for feb means ...selected month -1  like way...

            • 3. Re: Date Calculation
              Amanjot Klair

              Rahul Just create a new calc field

               

              NextMonth = CASE [Month]

                                                      WHEN "Dec" THEN "Jan"

                                                      WHEN "Jan" THEN "Feb"

                                                      WHEN "Feb" THEN "Mar"

                                                      WHEN "Mar" THEN "Apr"

                                                      WHEN "Apr" THEN "May"

                                                      WHEN "May" THEN "Jun"

                                                      WHEN "Jun" THEN "Jul"

                                                      WHEN "Jul" THEN "Aug"

                                                      WHEN "Aug" THEN "Sep"

                                                      WHEN "Sep" THEN "Oct"

                                                      WHEN "Oct" THEN "Nov"

                                                      WHEN "Nov" THEN "Dec"

                                       END

               

              Put this field into filters and use this field as quick filter this should solve your problem. If this doesn't works please share a .twbx workbook.

               

              -Amanjot

              1 of 1 people found this helpful
              • 4. Re: Date Calculation
                Vasily Lavrov

                Could you attach the sample of your workbook?

                It'll be easy to understand your goals and identify the problems

                • 5. Re: Date Calculation
                  Rahul Panday

                  Hi Amanjot ,

                  I tried same calculation provided by Vaisly .but I am getting NULL for this calculation.

                  I can not send you TWBX ..see I have month that is having 12 month (2015,2016) eg.jan15,feb15,mar15,apr15,may15,june15,jan16,feb16,mar16 like way,

                  I have separate year(15,16) ,qtr(15,16),week (16).

                   

                  so, I should have month & year filter ..when I select year 15 & jan15 then view should show data of feb15(2015)..month should be in view .

                  • 6. Re: Date Calculation
                    Amanjot Klair

                    I think i got your issue... What we are alligning here is a 12 month period only however you have a more then 12 month period. Do you want Dec15 to be displayed when Jan16 is selected?

                     

                    Also when it comes to Jan17 do you want only Dec16 or Dec16+Dec15?

                     

                    If the Dec16+Dec15 is ok with you then include LEFT([Month],3) instead of [Month] in the case statement

                    • 7. Re: Date Calculation
                      Rahul Panday

                      No No not like that . simple I have year string column  that is having two year  ( 2015 & 2016) , month string column ( for both 15 & 16 ).

                      I have one table that is showing some KPI say sales % based on some boolean condition that I have used in filter shelf ,now

                      when user selects month's filter (  jan-15) & year's filter ( year -15) then table should  show data of month  dec-15  & year-15 , if user selects month jan-16 & year-15 then

                      table should  show data of month  dec-16 & year-15 ..like way it should have for all months ..based on selection .means ..view should show previous month's data .

                      • 8. Re: Date Calculation
                        Rahul Panday

                        sorry I correct my 4th & 5th line :

                        if user selects month jan-16 & year-16 then

                        table should  show data of month  dec-16 & year-16..like way it should have for all months ..based on selection .means ..view should show previous month's data .

                        • 9. Re: Date Calculation
                          Vasily Lavrov

                          Oh, so complicated without .twbx)) 0_o

                           

                          I advise you to try parameters for your goals.

                          I attached an example. See how you can choose needed month with parameter based on its id.

                          Maybe it helps you in your further thoughts.

                          1 of 1 people found this helpful
                          • 10. Re: Date Calculation
                            Amanjot Klair

                            Rahul, The solution i recomended should solve this. In that only months are being compared so if the user is choosing the year and month both that calculation will only compare the months and year will be ignored. which means whatever year is selected the solution will simply go one month back and bring it from the same year... If you are giving a quick filter to the users try it on your workbook and there shouldn't be any issue.

                            • 11. Re: Date Calculation
                              Rahul Panday

                              Hi Amanjot & Vasily ,

                               

                              now , case statement is working fine..thanx a lot guys ..i was doing little

                              mistake ,thts why was getting error like Null.

                               

                              Thank You.

                               

                              On Thu, Feb 25, 2016 at 3:57 PM, Amanjot Klair <tableaucommunity@tableau.com

                              • 12. Re: Date Calculation
                                Amanjot Klair

                                That's great Rahul..!! but don't forget to the mark the replies as helpful or correct. It helps other users to find a solved a post.