3 Replies Latest reply on Jun 27, 2018 6:21 AM by suvas.chandra

    Date Calculation


      I have a Date field: QE Date
      I also have a parameter: Quarter End
      Quarter End was created from QE Date. Data type of Quarter End is string. I cannot change the data type for other reasons.

      Display format for Quarter End is: 2017 Q4, 2016 Q2 etc.

      Now, I want to show last five years data based on the parameter selection. I have Quarterly data. For yearly data, it should show data for Q4 only
      except for current selection.


      Use Case 1:

      If user choose 2016 Q2, data should display for: 2016 Q2, 2015 Q4, 2014 Q4, 2013 Q4, 2012 Q4

      Use Case 2:

      If user choose 2017 Q3, data should display for: 2017 Q3, 2016 Q4, 2015 Q4, 2014 Q4, 2013 Q4

      Use case 3:

      If user choose 2017 Q1, data should display for: 2017 Q1, 2016 Q4, 2015 Q4, 2014 Q4, 2013 Q4


      Any help ?


      Please remember, parameter is string format. Please feel free to use any data and any date field you have.

        • 1. Re: Date Calculation
          Shinichiro Murakami

          HI Suvas


          something like this.









          1 of 1 people found this helpful
          • 2. Re: Date Calculation



            It shows data for the last five years. But the data is not correct. It displays the biggest number of the quarters. That is probably I have used the Max() function. Here is another formula which I have used.


            Can you help me anything about this ?


            Case [Select an Attribute]

            WHEN "IRR"          THEN   STR(ROUND(MAX([ATTR GrossIRR]),1))
            WHEN "TVPI"         THEN   STR(ROUND(MAX([ATTR GrossTVPI]),2))
            WHEN "DPI"          THEN   STR(ROUND(MAX([ATTR GrossDPI]),2))
            WHEN "RVPI"         THEN   STR(ROUND(MAX([ATTR GrossRVPI]),2))
            WHEN "Paid In"      THEN   STR(ROUND(MAX([ATTR TotFunding]/1000000),1)) + "" + "M"
            When "Distribution" THEN   STR(ROUND(MAX([ATTR TotDist]/1000000),1)) + "" + "M"
            When "NAV"          THEN   STR(ROUND(MAX([Attr Nav]/1000000),1)) + "" + "M"
            When "Total Value"  THEN   STR(ROUND(MAX(([Attr Nav]+[ATTR TotDist])/1000000),1)) + "" + "M"
            When "Total Gain"   THEN   STR(ROUND(MAX([ATTR TotGain]/1000000),1))+""+"M"



            This is the final output. I displayed in year format:


            • 3. Re: Date Calculation

              Never mind. I have already figured it out.