Second MaxString

[#1]

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?

Thanks in Advance!

[sunny_talwar]

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

LOAD Quarter,

          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)

[sunny_talwar · Accepted solution]

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

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

[#4]

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?

[Anonymous]

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)

[sunny_talwar]

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

[#7]

Awesome - this works perfect!

[#8]

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.

Thanks for your help though!