Max value from a rolling quarter

[newqlik2017]

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)

[vkish16161]

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

[Anil_Babu_Samineni]

If you use Max() then ??

[newqlik2017]

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

[sunny_talwar]

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

[effinty2112]

HI Subash,

Try

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

Regards

Andrew

[newqlik2017]

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).

Any ideas, please?

[sunny_talwar · Accepted solution]

Check the attached now

[newqlik2017]

Thank you Sunny.