topic Second MaxString in App Development

Second MaxString

Wed, 15 Feb 2017 22:22:32 GMT

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!

Re: Second MaxString

sunny_talwar — Thu, 16 Feb 2017 09:27:04 GMT

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)

Re: Second MaxString

sunny_talwar — Thu, 16 Feb 2017 09:28:13 GMT

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

Thu, 23 Feb 2017 06:29:47 GMT

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

Anonymous — Thu, 23 Feb 2017 10:03:25 GMT

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

sunny_talwar — Thu, 23 Feb 2017 10:06:05 GMT

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

Re: Second MaxString

Tue, 28 Feb 2017 02:18:57 GMT

Awesome - this works perfect!

Re: Second MaxString

Tue, 28 Feb 2017 02:22:33 GMT

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!