Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
)
Works in Qlik Sense also
Your expression seems to be working for me
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)
Works in Qlik Sense also
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.
Additional column? What was that assumption you were making?
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)
)
Got it... thanks for sharing this additional information