topic Re: Sorting Dates in Chart in QlikView

Sorting Dates in Chart

felcar2013 — Fri, 14 Aug 2015 09:22:05 GMT

i want to sort the months (without year specification) in the order of the month_id (which refers to a corresponding "month_year" field)

i have attached the test app

I have Chart 1 and Chart 2. Chart 1 is sorted according to %month_id. I want as final result the Chart 2, which is a difference of values of current month minus a previous set of months. I want the value difference expressed as a month without specifying the year, but this shall be sorted (as showed in Chart 2).

In Chart 2, instead of displaying Sep.2014, it shall be displayed only " Sep ".

Calculation: "Value of Sep.2014 (84) - Value of Sep.2013 (83) " is showed as the "difference" which shall be displayed in Chart 2, but sorted as Sep.2014. The final value is " -1 ". The next value is the difference between Aug.2014 and Aug.2013 displayed as "Aug" and so on.

Thanks for any help on this

Here the test Table:

table_tmp:
load * inline [
month_id,%month_id,month_year_text,month_text,TY,LY,Diff
41883,81,Sep.2014,Sep,84,0,84
41852,80,Aug.2014,Aug,112,0,112
41821,79,Jul.2014,Jul,148,0,148
41791,78,Jun.2014,Jun,115,0,115
41760,77,May.2014,Mai,89,0,89
41730,76,Apr.2014,Apr,101,0,101
41699,75,Mar.2014,Mrz,110,0,110
41671,74,Feb.2014,Feb,107,0,107
41640,73,Jan.2014,Jan,140,0,140
41609,72,Dec.2013,Dez,100,0,100
41579,71,Nov.2013,Nov,89,0,89
41548,70,Oct.2013,Okt,90,0,90
41518,69,Sep.2013,Sep,0,83,-83
41487,68,Aug.2013,Aug,0,92,-92
41456,67,Jul.2013,Jul,0,98,-98
41426,66,Jun.2013,Jun,0,72,-72
41395,65,May.2013,Mai,0,76,-76
41365,64,Apr.2013,Apr,0,80,-80
41334,63,Mar.2013,Mrz,0,72,-72
41306,62,Feb.2013,Feb,0,81,-81
41275,61,Jan.2013,Jan,0,103,-103
41244,60,Dec.2012,Dez,0,76,-76
41214,59,Nov.2012,Nov,0,84,-84
41183,58,Oct.2012,Okt,0,90,-90
]
;

Re: Sorting Dates in Chart

rubenmarin — Fri, 14 Aug 2015 09:34:52 GMT

Hi Felipe, you can sort using this expression:

If(Month(Min(month_year_date))>=10, Month(Min(month_year_date))-12, Month(Min(month_year_date)))

Re: Sorting Dates in Chart

Gysbert_Wassenaar — Fri, 14 Aug 2015 09:35:50 GMT

Try sorting by Load Order Reversed or by expression =max({1}%month_id)

Re: Sorting Dates in Chart

felcar2013 — Fri, 14 Aug 2015 12:05:42 GMT

thanks, no, it did not work

i adjusted the test application, to reflect better the real app

Chart 2, under the given conditions, cannot be sorted with expressions or load order

see the attached app !

Re: Sorting Dates in Chart

felcar2013 — Fri, 14 Aug 2015 12:12:59 GMT

Hi Ruben

it did not work, i posted an adapted application

thanks

felipe

Re: Sorting Dates in Chart

rubenmarin — Fri, 14 Aug 2015 12:15:27 GMT

Hi Felipe, I don't understand what isn't working, I tested both options proposed by gysbert and mine.

If I select May.14 with the three options it shows months from Dez to Mai. Wich result you expect?

Re: Sorting Dates in Chart

Gysbert_Wassenaar — Fri, 14 Aug 2015 12:23:09 GMT

Try =max({$<month_year_text=,%month_id ={">$(=$(vFrom))<=$(=$(vTo))"}>}month_id)

Re: Sorting Dates in Chart

felcar2013 — Fri, 14 Aug 2015 12:24:16 GMT

i expect the Dimension "month" sorted from, say apr,march,feb,jan,dec,nov,oct,,etc. in that sequence.

Ex. if i select in the app

prev_month = 6

month_year_text = Mar.2014

i get this, when i have to get Mrz,Feb,Jan,Dez,etc.

Re: Sorting Dates in Chart

felcar2013 — Fri, 14 Aug 2015 12:26:22 GMT

perfect! thanks

Re: Sorting Dates in Chart

jonathandienst — Fri, 14 Aug 2015 12:29:58 GMT

If you load the data a little differently, this becomes quite simple:

table_tmp:

LOAD Date(month_id, 'MMM.YYYY') As DateMY,

Month(month_id) As Month,

%month_id,

TY,

LY,

Diff

inline

[

month_id,%month_id,month_year_text,month_text,TY,LY,Diff

41883,81,Sep.2014,Sep,84,0,84

41852,80,Aug.2014,Aug,112,0,112

41821,79,Jul.2014,Jul,148,0,148

...

Now DateMY is a dual field which you can sort numerically. The other two date fields are redundant as all the information is in DateMY and %month_id.

Re: Sorting Dates in Chart

felcar2013 — Fri, 14 Aug 2015 12:42:12 GMT

thanks, i will try this in the real app, since i have there 700 m. rows, however the max solution actually works fast