7 Replies Latest reply: Feb 27, 2017 9:22 PM by Prashant Kumar

# Second MaxString

Hi,

I am new to Qlik Sense and would be very grateful if someone could help me out with this problem.

I have a field called Quarter which has the following values

FY1516-Quarter 2

FY1516-Quarter 3

FY1516-Quarter 4

FY1617-Quarter 1

FY1617-Quarter 2

I want to find the sum of another field called value for the highest quarter and the second highest quarter selected i.e. if the last 3 are selected in the above list, I should get the sum of values for FY1617-Quarter 2 and FY1617- Quarter 1.

I have got the value for FY1617-Quarter 2 with the following

Sum({\$<Quarter={"\$(=MaxString(Quarter))"}>}Value)

However, I am not sure how to obtain the value for FY1617-Quarter 1. Can someone please help me out?

• ###### Re: Second MaxString

I would recommend to create a new field in the script to do this

KeepChar(Quarter, '0123456789') as QuarterNum

....

FROM ....;

and then these:

Highest Quarter

Sum({\$<QuarterNum={"\$(=Max(QuarterNum))"}, Quarter>}Value)

Second Highest Quarter

Sum({\$<QuarterNum={"\$(=Max(QuarterNum, 2))"}, Quarter>}Value)

• ###### Re: Second MaxString

On the front end, you can try this for 2nd highest

Sum({\$<Quarter = {"=KeepChar(Quarter, '0123456789') = Max(TOTAL KeepChar(Quarter, '0123456789'), 2)"}>}Value)

• ###### Re: Second MaxString

Hi Sunny,

Thanks for this. However, I believe I am at a much lower level with respect to Qlik Sense to comprehend correctly.

Can you please tell me where I should add the new field - is it in the data load editor?

• ###### Re: Second MaxString

Yes that's where I intend you to create a new field

• ###### Re: Second MaxString

Awesome - this works perfect!

• ###### Re: Second MaxString

This will get you the maxstring quarter minus one. It would be better to put this in a variable then use in expression;

=Left(Maxstring(Qtr),15) & num(Right(Maxstring(Qtr),1)-1)

In the Expression;

=sum({<Quarter={"\$(=Left(Maxstring(Quarter),15) & num(Right(Maxstring(Quarter),1)-1))"}>}Value)

• ###### Re: Second MaxString

Hi Gareth - this isn't working in certain situations as if I have a bunch of years, when I select only the first quarter of a year, the expression calculates the value as 0 instead of going back go 4th quarter of the previous year. Sunny's solution above worked well.