10 Replies Latest reply: Sep 6, 2013 9:40 AM by Erik Furlanis

# [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?

• ###### Re: [set analysis] Sort by highest quantity in most recent timestamp

Is timestamp being used as a dimension?

• ###### Re: [set analysis] Sort by highest quantity in most recent timestamp

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

and sum(BO_KPI.Qty) as expression

• ###### Re: [set analysis] Sort by highest quantity in most recent timestamp

You might get there with " "

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

• ###### Re: [set analysis] Sort by highest quantity in most recent timestamp

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!

• ###### Re: [set analysis] Sort by highest quantity in most recent timestamp

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.

• ###### Re: [set analysis] Sort by highest quantity in most recent timestamp

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.

• ###### Re: [set analysis] Sort by highest quantity in most recent timestamp

You might find this link useful:  The magic of set analysis - syntax and examples

• ###### Re: [set analysis] Sort by highest quantity in most recent timestamp

Thanks Vegar, it was in my Bookmarks Bar already, guess I need to refresh my memory with it!

• ###### Re: [set analysis] Sort by highest quantity in most recent timestamp

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