7 Replies Latest reply: Jun 7, 2017 7:01 AM by Manish Kachhia RSS

    Calculated Dimension

    Ravi Kishore

      Hi Friends,

       

      I am stuck in a Calculation

       

      I have Data for Last 4 Years.

       

      But i would want to show only For latest 3 years and latest 12 Quarters and Latest 36 Months only.

       

      I am able to derive this for years with Calculated Dimension. =If(Year>=$(vYears),Year)

      vYears=MAX(Year,2)-1

       

      But i am unable to create the same for Quarters and Months. If you see below image i have Quarters info from 2014 Q1 to 2017 Q3

       

      I would like to show in my graph only 2015 Q1 to 2017 Q3 : Only Latest 12 Quarters Always.

       

      Attached Sample Data file too.

      Quarters.JPG

       

      Capture.JPG

      Please can you give me some pointers to it.

        • Re: Calculated Dimension
          Manish Kachhia

          I am assuming that YearQuarter field you have created in Master Calendar.

          You can create Dual field for YearQuarter something like below.

          Change according to your need. This is just an example.

           

          Dual(Year & '-' & Quarter , AutoNumber(Year & '-' & Quarter,'YearQuarter'))  as YearQuarter

           

          and use as below calculated dimension

          =IF(YearQuarter >= (Max(Total YearQuarter)-11) and YearQuarter <= Max(Total YearQuarter), YearQuarter)

           

          and ignore filter of YearQuarter in your expression

           

          SUM({<YearQuarter>}YourExpression)

           

          Also if you want last 12 quarters irrespective of Quarter or year selection then use as below

          =IF(YearQuarter >= (Max({1}Total YearQuarter)-11) and YearQuarter <= Max({1}Total YearQuarter),YearQuarter)