Announcements
cancel
Showing results for
Did you mean:
Creator II

## Max value from a rolling quarter

Below is few examples of a field that has rolling quarter. These values are derived from a date column (YYYYMM format).

How to achieve the result as seen below in a chart? It's always 4 rolling quarters. Minstring and Maxstring doesn't always give correct result.

1)

2Q 2016
3Q 2016
4Q 2016
1Q 2017
Result = 1Q 2017

2)

3Q 2016
4Q 2016
1Q 2017
2Q 2017
Result = 2Q 2017

3)

4Q 2016
1Q 2017
2Q 2017
3Q 2017
Result = 3Q 2017

4)

4Q 2017
3Q 2017
2Q 2017
1Q 2018
Result = 1Q 2018

Minstring(QtrYr)
Maxstring(QtrYr)

1 Solution

Accepted Solutions
MVP

Check the attached now

8 Replies
Creator III

You can try FirstSortedValue. You need to be more specific on what the requirement is.

If you use Max() then ??

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Creator II
Author

MIN n MAX functions work for numeric data type. In this example, the values are string (non numeric) type. So MAX() doesn't work.

MVP

May be assign this field an underlying numeric value...

LOAD Dual(Pick(WildMatch(QuarterYear, '1Q*', '2Q*', '3Q*', '4Q*'), 3, 6, 9, 12) + Right(QuarterYear, 4), QuarterYear) as QuarterYear

and now max and maxstring will both work

Master

HI Subash,

Try

=Div(Month(max(Date))-1,3)+1 & 'Q ' & Year(max(Date))

Regards

Andrew

Creator II
Author

Hi Andrew and Sunny,

Thank you for your response. I tried both the option but didn't seem to work.

I attached a sample QVW. Basically, I need to get the latest value of the rolling quarter - either in YYYYMM format or <Qtr_Num>Q YYYY (for instance 1Q 2017).