11 Replies Latest reply on Jun 3, 2017 6:38 PM by suresh satti

    Custom Fiscal Year & Quarters

    Tausif Khan

      Hey Guys,

       

      I am using a custom Fiscal year (Oct-Sept) and have applied filters. The issue is with the Quarters. when I select "All" it filters fine.. the issue occurs when I filter a Specific Quarter. Though it shows the correct data - But I have to click on Q1 to see Q2 Results (As my Fiscal Year Starts from Oct). Is there a way by which I can correct this?

       

      I am stuck - Please help.

       

      Thank you for your help in Advance.

       

      Regards,

      Tausif

        • 1. Re: Custom Fiscal Year & Quarters
          Shinichiro Murakami

          I'm not sure you already tried this or not...

          Tableau has great feature to handle "Dates" and there is default setting on Fiscal Year start month.

          You can get that through below instruction.

           

           

           

           

          Thanks,

          Shin

           

          9.2 attached.

          2 of 2 people found this helpful
          • 2. Re: Custom Fiscal Year & Quarters
            Tausif Khan

            Thank you for your quick response Murakami Sama,

             

            I did exactly what you have suggested, the problem is  that it is not displaying the same when I apply filter on Quarter - Please check the attached image  .

             

            Regards,

            Tausif.

            • 3. Re: Custom Fiscal Year & Quarters
              Amanjot Klair

              Tausif, did you change your date level to use Date(copy) instead of Date, also in your view i don't see you using Date(copy) anywhere. The solution Shin mentioned should work for your case without any issue.

               

              -Amanjot

              • 4. Re: Custom Fiscal Year & Quarters
                Shinichiro Murakami

                I don't know you have some other difficulty with below solution or not, but anyway..

                You can create your own fiscal quarter.

                 

                [Fiscal Year]

                if month([Date])>=10 then year([Date])+1 else year([Date]) END

                 

                [Quarter]

                "Q"+str(int(((month([Date])+3-1)%12)/3)+1)

                 

                [Fiscal Month]

                str([Fiscal Year])+" "+right(str(100+month([Date])),2)+datename('month',[Date])

                 

                [Year-Qtr]

                str([Fiscal Year])+" "+[Quarter]

                 

                [DateLevelCalc]

                CASE [Date Level]

                WHEN "Week" THEN

                  STR(DATETRUNC('week',[Date]))+" - "+STR(DATETRUNC('week',[Date])+6)

                WHEN "Month" THEN

                  [Fiscal Month]

                WHEN "Quarter" THEN

                  [Year-Qtr]

                WHEN "Year" THEN

                  STR([Fiscal Year])

                END

                 

                Thanks,

                Shin

                5 of 5 people found this helpful
                • 5. Re: Custom Fiscal Year & Quarters
                  Tausif Khan

                  Amanjot,

                   

                  I do not need to Create a Copy of the Date Field  as I have already set the Fiscal Calendar to start at October.

                  Ideally, I would want this solution to work. But since It is not working I would have to create a Custom Quarter Field.

                   

                  Cheers!

                  Tausif

                  • 6. Re: Custom Fiscal Year & Quarters
                    Tausif Khan

                    Thank you Shinichiro, I will try this out.

                     

                    I also happen to write to the Tableau Tech Support and They agreed that this is a shortcoming in Tableau. Their Response is as below -

                     

                     

                     

                    I  notice that the filer is based on a calculated field  which applies data functions like DATETRUNC() and DATENAME() to the fiscal year data field . Currently the ability to use the fiscal year dates in Tableau date function calculated fields is not built into the product. I believe that is why we could not have expected quarters shown in the Filter list.

                    I understand this would be a great feature to add to our product. Unfortunately I cannot provide you with a time frame for the release of this feature, but I am going to add this case to the existing enhancement request for the Product Management team to review for possible inclusion in a future release.

                     

                     

                    Thanks again for your help really appreciate it. Will let you know how it goes.

                     

                    Regards,

                    Tausif

                    1 of 1 people found this helpful
                    • 7. Re: Custom Fiscal Year & Quarters
                      Shinichiro Murakami

                      Thank you for the info.

                       

                      BTW, could you mark the answer as "Correct" or "Helpful" if you are OK>

                       

                      Shin

                      2 of 2 people found this helpful
                      • 8. Re: Custom Fiscal Year & Quarters
                        Garrett Hills

                        Can you explain the logic behind this, if for instance I wanted my Fiscal Year to start July 31st and have my Q4 run to July 29th how would I edit the above equations for each?

                        • 9. Re: Custom Fiscal Year & Quarters
                          Shinichiro Murakami

                          Garrett

                           

                          Fiscal Calendar is really depend on the company.

                          There should be some commonalities, but still have several types.

                          If you can attach your X-reference between calendar date and Fiscal Week/Month/Quarter/Year, I can analyze it.

                           

                          Also if you need immediate help, you can post another thread with mentioning me.

                           

                          Thanks,

                          Shin

                          • 10. Re: Custom Fiscal Year & Quarters
                            venkat g

                            Hi Murakami,

                             

                            After changing fiscal year starting month to October able to get exactly wat iwant i.e able to see oct,Nov,Dec under Q1.

                             

                            But when i checked MONTH(Order Date) still Tableau is showing oct=10 only. But i want to see oct=1,Nov=2,Dec=3,jan=4,feb=5 ...etc.

                            Any idea on above hw to set fiscal year starting month =1?

                             

                            Thanks,

                            venkat.

                            • 11. Re: Custom Fiscal Year & Quarters
                              suresh satti

                              Hi Venkat,

                               

                              This is not a good solution...... just for your reference

                               

                              FY_MONTH

                              str([FinancialYear])+" "+

                              STR(

                              CASE  month([My Order Date])

                              When 1 then 4

                              WHEN 2 then 5

                              when 3 then 6

                              when 4 then 7

                              when 5 then 8

                              when 6 then 9

                              when 7 then 10

                              when 8 then 11

                              when 9 then 12

                              when 10 then 1

                              when 11 then 2

                              when 12 then 3

                              END

                              )+" "+datename('month',[My Order Date])