Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

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?

Thanks in Advance!

Tags (1)
1 Solution

Accepted Solutions
sunny_talwar
Not applicable

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)

7 Replies
sunny_talwar
Not applicable

Re: Second MaxString

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
Not applicable

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)

Not applicable

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?

gareth_wilson12
Not applicable

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)

sunny_talwar
Not applicable

Re: Second MaxString

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

Not applicable

Re: Second MaxString

Awesome - this works perfect!

Not applicable

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.

Thanks for your help though!