Set analysis within set analysis | Qlik Sense

[#1]

I need to sum anything with a Jrnl_number which is largest than the maximum Jrnl_number for a particular LineRef (here: 'Balance Brought Forward')

LineRef	Jrnl_number	Value
A	11	100
B	12	200
C	13	300
'Balance Brought Forward'	14	350
'Balance Brought Forward'	15	400
E	16	450
F	17	600

In this example the output should be 450 + 600 = 1,050.

I have tried the below but it doesn't seem to work. My mental logic was that a set analysis within a set analysis might work (but I suspect that's not the case).

sum(

  {$<

        Jrnl_number = {">$(=max({$<LineRef = {'Balance Brought Forward'}>}(Jrnl_number)))"} >}

      (Value)

  )

[sunny_talwar]

Your expression seems to be working for me

[sunny_talwar]

Does the data have single quotes around Balance Brought Forward? If it does, then you can try this

=Sum({$<Jrnl_number = {">$(=max({$<LineRef = {'*Balance Brought Forward*'}>}Jrnl_number))"}>}Value)

[sunny_talwar · Accepted solution]

Works in Qlik Sense also

[#5]

Thanks for the help. Turns out the syntax was correct, I just had an incorrect assumption about an additional column. I'll mark your answer as correct.

[sunny_talwar]

Additional column? What was that assumption you were making?

[#7]

Essentially I needed to restrict the data to a date (both in what was being summed up and in finding the maximum JnL number), so the revised logic became something like this.

sum(

{$<

[TRX Date]= {"<=$(=date(MakeDate(year(max(total([TRX Date])))-1,12,31),'DD/MM/YYYY HH:MM:SS'))>=$(=date(MakeDate(year(max(total([TRX Date])))-1,12,30),'DD/MM/YYYY HH:MM:SS'))"},

Jrnl_number = {">$(=max({$<LineRef = {'Balance Brought Forward'},[TRX Date]= {"<=$(=date(MakeDate(year(max(total([TRX Date])))-1,12,31),'DD/MM/YYYY HH:MM:SS'))>=$(=date(MakeDate(year(max(total([TRX Date])))-1,12,30),'DD/MM/YYYY HH:MM:SS'))"}>}(Jrnl_number)))"}

>}

(Value)

)

[sunny_talwar]

Got it... thanks for sharing this additional information