8 Replies Latest reply on Jul 5, 2018 11:51 PM by Zhouyi Zhang

    Bring Fiscal Date when a time period is selected

    Ophelia Daisy

      Hi All,

       

      I am into trouble again I want to bring the respective Fiscal Date when a time period is selected.

       

      Details: My Fiscal Year starts from July - June. If July 2017 - June 2018, My Fiscal Year is called as FY 2018.

       

      Now, When i select FY 2016 December (as given below) from a parameter ( values added from custom date field which adds fiscal to my calendar), I should get the period displayed in the title as From 07/01/2015 - 12/20/2015 and so on.

       

       

      Attached the sample workbook for the same.

       

      Thanks in Advance.

        • 1. Re: Bring Fiscal Date when a time period is selected
          Tushar  More

          Hi Ophelia,

           

          Are you looking for something like this?

           

           

          ~Tushar

          • 2. Re: Bring Fiscal Date when a time period is selected
            Ophelia Daisy

            Hi Tushar,

             

            Thank you for the response. Yeah. Something similar to this.


            When i select FY 2016 December (as given below) from a parameter ( values added from custom date field which adds fiscal to my calendar), I should get the period displayed in the title as From 07/01/2015 - 12/31/2015 and so on. 

             

            My Fiscal Year starts from July - June. If July 2017 - June 2018, My Fiscal Year is called as FY 2018. Based on the time period I select from the parameter, my title should be changed for that fiscal year start date and the end date of the selected time period.

             

            Let me know if you have any more queries.


            Thanks

            • 3. Re: Bring Fiscal Date when a time period is selected
              Norbert Maijoor

              Hi Ophelia,

               

              Not sure;) but find my "attempt" as reference below and stored in attached workbook version 10.2 located in the original thread.

               

               

              1. D1. Order Date (Months):

               

              2. D2. Order Date (Years)

               

              3. D3. Month: month([Order Date])

               

              4. D4. Month Converter

              if [D3. Month]=1 then "juli"

              elseif [D3. Month]=2 then "augustus"

              elseif [D3. Month]=3 then "september"

              elseif [D3. Month]=4 then "october"

              elseif [D3. Month]=5 then "november"

              elseif [D3. Month]=6 then "december"

              elseif [D3. Month]=7 then "januari"

              elseif [D3. Month]=8 then "februari"

              elseif [D3. Month]=9 then "march"

              elseif [D3. Month]=10 then "april"

              elseif [D3. Month]=11 then "may"

              elseif [D3. Month]=12 then "june"

              end

               

              5. D5. FY Year Month: "FY"+" "+str(int(mid(str([D2. Order Date (Years)]),1,4))+1)+" "+str([D4. Month converter])

               

              6. P1. FY Year Month Parameter

               

               

              7. D6. Display Month:

              if int(mid([P1. FY Year Month Parameter],4,4))-1=int([FY Year])

              and [D3. Month]>=7 then [D1. Order Date (Months)]

              elseif int(mid([P1. FY Year Month Parameter],4,4))=int([FY Year])

              and [D3. Month]<7 then [D1. Order Date (Months)] END

               

              8. Drag required objects to the indicated locations, exclude null from D6. Display Month and show parameter control

               

               

              Regards,

              Norbert

              • 4. Re: Bring Fiscal Date when a time period is selected
                Zhouyi Zhang

                Hi, Ophelia

                 

                A bit of confuse, but please find my solution attached.

                The only thing is, the parameter value can't match the display value, I am not sure whether you did it on purpose or not.

                 

                Hope this could help

                 

                ZZ

                • 5. Re: Bring Fiscal Date when a time period is selected
                  Ophelia Daisy

                  Hi Zhouyi,

                   

                  Thanks for the solution. But then, when i click on FY 2015 January, it is showing the year as 2016. Ideally the fiscal year should be displayed as FY 2015.

                   

                  I am stuck with this. Please help.

                   

                  -Ophelia

                  • 6. Re: Bring Fiscal Date when a time period is selected
                    Zhouyi Zhang

                    Hi, Ophelia

                     

                    In my workbook, when I select FY 2015 January, it returns the correct result

                     

                    Can you show me your test case?

                     

                    ZZ

                    • 7. Re: Bring Fiscal Date when a time period is selected
                      kavi ram

                      Hi Zhouyi Zhang,

                      I am facing here the similar issue but little different what i need,

                      my order date calendar is start from Jan to Dec and i am trying to display Year to date data . but now some user wants the fiscal calendar should start with July to June(next year) .

                      Example:

                      My current function is working as below,

                       

                      [Order Date] >= date('01/01'+"/"+str(YEAR([Month Selector])))

                      and DATETRUNC('month',[Order Date]) <= DATETRUNC('month', [Month Selector])

                       

                      when i select month parameter as Jan 2015 then the value is displaying jan2015, if month parameter is March 2015 then the value is displaying from jan2015 to march2015 and so on.

                       

                      So i have changed the formula above like,

                      [Order Date] >= date('01/07'+"/"+str(YEAR([Month Selector])))

                      and DATETRUNC('month',[Order Date]) <= DATETRUNC('month', [Month Selector])

                       

                      Now the values are showing from July to Dec only. i need like below functionality,

                      if my month parameter is Dec2015 then Year to date value as July2015 to Dec2015

                      if my my month parameter is June2016 then Year to date value as July2015 to June2016

                       

                      I have attached the workbook also,

                       

                      Thanks,

                      Kavi

                      • 8. Re: Bring Fiscal Date when a time period is selected
                        Zhouyi Zhang

                        Hi, Kavi

                         

                        I replied in your other thread with attachment, please have a look and let me know if you still have question.

                         

                        ZZ