7 Replies Latest reply: Mar 23, 2017 7:07 AM by Sunny Talwar

# Set analysis within set analysis | Qlik Sense

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')

LineRefJrnl_numberValue
A11100
B12200
C13300
'Balance Brought Forward'14350
'Balance Brought Forward'15400
E16450
F17600

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)

)

• ###### Re: Set analysis within set analysis | Qlik Sense

Your expression seems to be working for me

• ###### Re: Set analysis within set analysis | Qlik Sense

Works in Qlik Sense also

• ###### Re: Set analysis within set analysis | Qlik Sense

Additional column? What was that assumption you were making?

• ###### Re: Set analysis within set analysis | Qlik Sense

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)

)

• ###### Re: Set analysis within set analysis | Qlik Sense

Got it... thanks for sharing this additional information

• ###### Re: Set analysis within set analysis | Qlik Sense

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)