2 Replies Latest reply on Aug 18, 2018 8:20 PM by Okechukwu Ossai

    Custom quarters QTD calculations

    Sonali chavan

      Hi Guys,

       

      I have 2 Quarters to show.

      1. Standard quarter

      2. Business Quarter. (Jan-feb: "Q1", MAR-MAY:"Q2"  , JUN-AUG: "Q3" , Sep-DEC :"Q4" )

       

      I need to show QTD sales.

       

      I can easily calculate QTD for standard quarters, but while calculating BQ I am stuck.

       

      Requirement is:

      if selected date is 2-Feb-2018 (Business quarter:  "Q1" ) then data should be display for 1 jan 18 (start of Business quarter) till 2nd feb.

      Similarly, for May 25 (1 Mar 18 - 25 Mar 18) and so on.

       

      Could someone suggest me something .

       

       

      Regards,

      Sonali.

        • 1. Re: Custom quarters QTD calculations
          Okechukwu Ossai

          Hi Sonali,

           

          Below is a solution using the Sample Superstore dataset. See attached workbook (version 10.1)

           

          Step 1: Create parameter [Select Date]

           

          Step 2: Create calculated field [Business Quarter]

          IF DATEPART('month', [Order Date]) >= 9 THEN 'Q4'

          ELSEIF DATEPART('month', [Order Date]) >= 6 THEN 'Q3'

          ELSEIF DATEPART('month', [Order Date]) >= 3 THEN 'Q2'

          ELSEIF DATEPART('month', [Order Date]) >= 1 THEN 'Q1'

          END

          Step 3: Create calculated field [Selected Business Quarter]

          IF DATEPART('month', [Select Date]) >= 9 THEN 'Q4'

          ELSEIF DATEPART('month', [Select Date]) >= 6 THEN 'Q3'

          ELSEIF DATEPART('month', [Select Date]) >= 3 THEN 'Q2'

          ELSEIF DATEPART('month', [Select Date]) >= 1 THEN 'Q1'

          END

           

          Step 4: Create calculated field [Date Filter]

          [Order Date] =

              (IF [Selected Business Quarter] = [Business Quarter] AND YEAR([Select Date]) = YEAR([Order Date]) THEN

              IF [Order Date] <= [Select Date] THEN [Order Date] END

          END)

          Add this to the filter shelf and set to 'True'

           

          Hope this helps.

          Ossai

           

           

          1 of 1 people found this helpful
          • 2. Re: Custom quarters QTD calculations
            Okechukwu Ossai

            Hi Sonali,

             

            If this resolved your question, could you please mark my answer as correct?

             

            Ossai