Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
newqlik2017
Creator II
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
sunny_talwar

Check the attached now

View solution in original post

8 Replies
vkish16161
Creator III
Creator III

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

Anil_Babu_Samineni

If you use Max() then ??

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
newqlik2017
Creator II
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.

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
Master
Master

HI Subash,

Try

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

Regards

Andrew

newqlik2017
Creator II
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).

Any ideas, please?

sunny_talwar

Check the attached now

newqlik2017
Creator II
Creator II
Author

Thank you Sunny.