Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

[set analysis] Sort by highest quantity in most recent timestamp

Dear all

I am quite familiarised with the Set Analysis, but yet there is something simple that I am not able to achieve.

Been giving it a try in these last two weeks, but I'am giving up!

Basically, my source is SKU, quantity, timestamp

What I want to visualise, in a pivot chart, is the trend of quantity available for each SKU over time.

And I would like the SKU to be sorted descending according to the SKU which has the highest quantity for the most recent timestamp

I tried with something like this (in Sort>Expression)

=sum({$<BO_KPI.TimeStamp={$(=max(BO_KPI.TimeStamp))}>}   BO_KPI.Qty)

which is a pretty straightforward set analysis, but apparently I still manage to do something wrong, as SKUs are sorted in a random way (or in a way with no apparent logic).

Can someone pls help me sort this out?

Thanks already!

1 Solution

Accepted Solutions
Vegar
MVP
MVP

You might get there with " "

=sum({$<BO_KPI.TimeStamp={"$(=max(BO_KPI.TimeStamp))"}>}   BO_KPI.Qty)

View solution in original post

10 Replies
tresesco
MVP
MVP

Is timestamp being used as a dimension?

Vegar
MVP
MVP

You might get there with " "

=sum({$<BO_KPI.TimeStamp={"$(=max(BO_KPI.TimeStamp))"}>}   BO_KPI.Qty)

Not applicable
Author

yes, the pivot is organised with TimeStamp and SKU as dimensions

and sum(BO_KPI.Qty) as expression

stigchel
Partner - Master
Partner - Master

Brice-SACCUCCI
Employee
Employee

Hi,

have you tried adding the expression

"=sum({$<BO_KPI.TimeStamp={$(=max(BO_KPI.TimeStamp))}>}  BO_KPI.Qty) "

to your table, to see the computed values used for sorting?

Regards,
Brice

Not applicable
Author

amazing!

two stupid quotation marks troubled me for so long!

can you please elaborate on what is the effect of the quot. marks?

thanks again!

Vegar
MVP
MVP

If the value inside ={ } is a text you should use quotation marks. It is not necessary if numeric. I'm guessing that your BO_KPI.TimeStamp might compare to the text of its dual value.


I find that using dual-values in set to be confusing at times.

Not applicable
Author

alrite!

indeed a common value of TimeStamp looks like

06-09-13 14:27

it is a mixture of digits and symbols, so text!

thanks for the duly explanation.

Vegar
MVP
MVP