topic Re: [set analysis] Sort by highest quantity in most recent timestamp in QlikView

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

Fri, 06 Sep 2013 13:03:35 GMT

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!

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

tresB — Fri, 06 Sep 2013 13:08:58 GMT

Is timestamp being used as a dimension?

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

Vegar — Fri, 06 Sep 2013 13:09:40 GMT

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

Fri, 06 Sep 2013 13:10:50 GMT

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

stigchel — Fri, 06 Sep 2013 13:11:13 GMT

Does this help:

http://community.qlik.com/ideas/1250

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

Brice-SACCUCCI — Fri, 06 Sep 2013 13:12:43 GMT

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

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

Fri, 06 Sep 2013 13:12:54 GMT

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

Vegar — Fri, 06 Sep 2013 13:25:29 GMT

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

Fri, 06 Sep 2013 13:28:37 GMT

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

Vegar — Fri, 06 Sep 2013 13:28:53 GMT

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

Fri, 06 Sep 2013 13:40:34 GMT

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