Nested Set Analysis in QlikSense

Question

My User_ID has several images and with this piece of code I receive the largest IMAGE_NR .

  max({$<USER_ID = {'8638087'}> } IMAGE_NR)

Every image number is linked to an IMAGE_ID. How do I get this?

In words:

Give me IMAGE_ID where IMAGE_NR = largest IMAGE_NR of my USER_ID

I tried following that doesn't work:

  sum({$<max({<USER_ID={'8638087'}> } IMAGE_NR)>} IMAGE_ID)

Thank you for any thoughts!

score 2 · Accepted Answer · answered Aug 20 '19 at 07:04

Here is a step-by-step solution.

You mention that this already brings back the correct IMAGE_NR:

max({$<USER_ID = {'8638087'}> } IMAGE_NR)

Now you want the IMAGE_ID. The logic is:

=only({CORRECT_IMAGE_NR_SET_ANALYSIS} IMAGE_ID)

I generally prefer to avoid search mode (double quotes inside element list) and instead use a higher evaluation level on the top calculation, so I would recommend:

$(=
    'only({<IMAGE_NR={'
    & max({$<USER_ID = {'8638087'}> } IMAGE_NR)
    & '}>} IMAGE_ID)'
)

This would also provide you a nice preview formula in the formula editor, something like:

only({<IMAGE_NR={210391287}>} IMAGE_ID)

score 1 · Answer 2 · answered Aug 13 '19 at 09:35

1

I didn't test it, but i believe it's something like :

only({<IMAGE_NR={'$(=max(IMAGE_NR))'}, USER_ID={'8638087'}>} IMAGE_ID)

answered Aug 13 '19 at 09:35

Idriss Benbassou

1,481
2
14
23

Should work, think all you need extra is the USER_ID set in the max() otherwise the max IMAGE_NR might be out of range for the selected USER_ID. only({} IMAGE_NR))'}, USER_ID={'8638087'}>} IMAGE_ID) – The Budac Aug 16 '19 at 09:56

score 1 · Answer 3 · answered Aug 16 '19 at 09:32

If the dimension of the table is USER_ID then this will return the IMAGE_ID of the maximum IMAGE_NR per USER_ID. Should work / return results for any dimension, but then will have to be read as maximum IMAGE_NR per whatever that dimesion is. The minus on IMAGE_NR is to get the largest/last sorted value

firstsortedvalue(IMAGE_ID,-IMAGE_NR)

If you're using it in a text box with no dimension then you can also add the set analysis

firstsortedvalue({<USER_ID={'8638087'}>} IMAGE_ID,-IMAGE_NR)

Nested Set Analysis in QlikSense

3 Answers3