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

HI Suvas

something like this.

Thanks,

Shin

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

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.

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

Never mind. I have already figured it out.