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

    Date Calculation

    suvas.chandra

      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.

           

           

           

           

           

           

          Thanks,

          Shin

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

            Shin,

             

            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"
            END

             

             

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

             

            • 3. Re: Date Calculation
              suvas.chandra

              Never mind. I have already figured it out.