Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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
Valued Contributor

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)

Please ekskuse my Norglish and Swenglish typos.
10 Replies
MVP
MVP

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

Is timestamp being used as a dimension?

Vegar
Valued Contributor

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)

Please ekskuse my Norglish and Swenglish typos.
Not applicable

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

stigchel
Honored Contributor

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

baa
Contributor

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

Not applicable

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!

Vegar
Valued Contributor

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.

Please ekskuse my Norglish and Swenglish typos.
Not applicable

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.

Vegar
Valued Contributor

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

Please ekskuse my Norglish and Swenglish typos.
Community Browser