12 Replies Latest reply on Jul 31, 2018 10:46 PM by kavi ram

    Fiscal year change for Regions

    kavi ram

      Tushar  More

       

      Hi Tushar,

      Your recent answer for thread/274875 is really helpful. and similar kind of logic i am facing and little different.  below is my scenario,

      1) Display Monthly (data should display last 6 months from selected month selector parameter)

      2) Display rolling 12 months (data should display last 12 months from selected month selector parameter)

      3) Display YTD (data should display last 6 months from selected month selector parameter  & also if the month parameter is > 6 then it should display the receptive fiscal year YTD any months)

       

      So the above 3 requirements are fine now,

      But now the problem i ma facing here, for particular region if i select then my YTD Fiscal year should July to Jun

       

      Here i have attached my workbook and the calculation,

       

      Here, in Region Select field i have mentioned if my region is 'West' then fiscal year should start from July else should start from Jan,

      so i ma facing problem when my region is west and if i have select month selector is Jan.

       

      Thanks,

      Kavi

        • 1. Re: Fiscal year change for Regions
          Bryce Larsen

          I can try to take a stab at it later, but initial thought:

          Make another date filter as shown above for West region (In Period - West) and then you can use this in a CASE statement:

          CASE [Region]

               WHEN "West"     THEN [In Period - West] = "In Period"

               ELSE                   [In Period] = "In Period"

          END

          and filter on true.

           

          I'd really advise not returning a string at all, really, as that can slow the performance as just returning T/F as a boolean is quicker.

           

          Also, perhaps it might be beneficial to create an FY Date flag based on the Region to use in your calculation instead:

           

          Also, can you confirm the FY range for the other regions? Thanks!

          • 2. Re: Fiscal year change for Regions
            kavi ram

            Hi Larsen,

             

            Thanks for your reply, but this is not i am looking..

            My logic is working fine for for all the regions including 'West'.

            But when my month parameter values is Jan 2015 then, 'West' region is displaying again Aug to Jan this result is fine for other regions not for 'West. So it should display July 2014 to Jan 2015 so on.. so my FY is July 2014 to June 2015..

             

            Please look into the attached workbook for more information.

             

            Thanks,

            Kavi

            • 3. Re: Fiscal year change for Regions
              kavi ram

              Hi Larsen,

               

              For other Regions FY range is Jan to Dec and for other Regions the calculation is working perfect. only i need the logic for 'West' post Dec month selection.

               

              Thanks,

              Kavi

              • 4. Re: Fiscal year change for Regions
                Bryce Larsen

                Yep, I think I understand!

                I think this is what you want to see then? When "Year To Date" is selected, "Month Selector"=January 2015, and "Depth month"=5, it shows August-January for all non-West regions, for the west it actually shows FYTD (July-January).

                 

                Is this accurate?

                 

                Final calculation:

                Caveat: Month Selector - FY Start and Order Date - FY Start are dependent on region. These are key to drive the calculation.

                You do the same as above but replace Month Selector with Order Date.

                 

                Twbx attached!

                 

                Best,

                Bryce

                • 5. Re: Fiscal year change for Regions
                  kavi ram

                  Hi Larsen,

                  Could you please downgrade your tableau version into 10.5 and attache the workbook again. i am using 10.5 version an i ma not able to open your workbook.

                   

                  Thanks,

                  Kavi

                  • 6. Re: Fiscal year change for Regions
                    Bryce Larsen

                    Yep! Sorry - usually I use 10.3 (version we use at work) but downloaded 2018.1 to work on things like this. Heh. Attached!

                    • 7. Re: Fiscal year change for Regions
                      kavi ram

                      Hi Bryce Larsen,

                       

                      Thank you very much !!!!! .. This is what exactly i want..

                       

                      Thanks once again.

                      Regards,

                      Kavi

                      • 8. Re: Fiscal year change for Regions
                        kavi ram

                        Hi Bryce Larsen,

                        The solution which you have provide to fiscal year change for region is working fine.

                        So, here the same for Region level i have created the running some for the months and again for the Region 'West' i got an issue due to Fiscal year start July.

                         

                        Here the scenario is , running sum for all the region fiscal year start Jan to Dec and for the region West fiscal year star July to Jun. and i need to show 14 months trend.

                        i am able to achieve this for all the regions jan to dec but for region west i need you help.

                         

                        Here i have attached the workbook,

                        Thanks,

                        Kavi

                        • 9. Re: Fiscal year change for Regions
                          kavi ram

                          Hi Bryce Larsen,

                          The solution which you have provide to fiscal year change for region is working fine.

                          So, here the same for Region level i have created the running some for the months and again for the Region 'West' i got an issue due to Fiscal year start July.

                           

                          Here the scenario is , running sum for all the region fiscal year start Jan to Dec and for the region West fiscal year star July to Jun. and i need to show 14 months trend.

                          i am able to achieve this for all the regions jan to dec but for region west i need you help.

                           

                          Here i have attached the workbook,

                          Thanks,

                          Kavi

                          • 10. Re: Fiscal year change for Regions
                            Bryce Larsen

                            I'm not really sure what you're trying to do here. Are you trying to show rolling 14 month sales for all regions in a trend?

                            Just like:

                            If so, you just need to edit your table calculation and select Specific Dimensions and do all values. That should be it. I also changed the MONTH to Discrete instead of continuous.

                            • 11. Re: Fiscal year change for Regions
                              kavi ram

                              Hi Bryce Larsen,

                              Thank you for your reply,

                              I am actually looking for cumulative sum for year to date for different fiscal year. so here i got the result for the calendar fiscal here for Jan to Dec (the cumulative value restarts every year of order date).please see the details below,

                               

                              Here the scenario,

                              Running sum for all the region fiscal year start Jan to Dec and for the region West fiscal year star July to Jun. and i need to show 14 months trend at most

                              i am able to achieve this for all the regions jan to dec but for region west i need you help.

                               

                              Here the formula i am using for running sum - Jan to Dec,

                               

                              Select date Display = [Order Date] <= DATEADD('month', 1, [Month Selector]) 

                              Depth Month display = last()<=[Actual Depth Month] 

                              Actual Depth Month value is = 13

                               

                              Below the image is for normal calendar fiscal year start Jan to Dec when my month selector is jan 2016 then the below image

                              is showing jan 2016(1 month) , previous jan 2015 to dec2015 (12 month s) & Dec 2014 (1) so total 14 months...

                               

                              So i need to do the same for fiscal year July to June when i my region is West.

                              i have attached workbook in the previous reply.

                               

                              Thanks in adv.

                              Regards,

                              kavi

                              • 12. Re: Fiscal year change for Regions
                                kavi ram

                                Hi Larsen,

                                Please let me know if any update.

                                 

                                Thanks,

                                Kavi