Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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!

1 Solution

Accepted Solutions
sunny_talwar

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

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

View solution in original post

7 Replies
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

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
Author

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

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

Not applicable
Author

Awesome - this works perfect!

Not applicable
Author

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!