Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
On the front end, you can try this for 2nd highest
Sum({$<Quarter = {"=KeepChar(Quarter, '0123456789') = Max(TOTAL KeepChar(Quarter, '0123456789'), 2)"}>}Value)
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)
On the front end, you can try this for 2nd highest
Sum({$<Quarter = {"=KeepChar(Quarter, '0123456789') = Max(TOTAL KeepChar(Quarter, '0123456789'), 2)"}>}Value)
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?
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)
Yes that's where I intend you to create a new field
Awesome - this works perfect!
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!