Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
newqlik2017
Contributor

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

Re: Max value from a rolling quarter

Check the attached now

View solution in original post

8 Replies
vkish16161
Contributor III

Re: Max value from a rolling quarter

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

Re: Max value from a rolling quarter

If you use Max() then ??

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
Contributor

Re: Max value from a rolling quarter

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

Re: Max value from a rolling quarter

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
Honored Contributor

Re: Max value from a rolling quarter

HI Subash,

Try

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

Regards

Andrew

newqlik2017
Contributor

Re: Max value from a rolling quarter

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?

Re: Max value from a rolling quarter

Check the attached now

View solution in original post

newqlik2017
Contributor

Re: Max value from a rolling quarter

Thank you Sunny.