Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi
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
]
;
Try =max({$<month_year_text=,%month_id ={">$(=$(vFrom))<=$(=$(vTo))"}>}month_id)
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)))
Try sorting by Load Order Reversed or by expression =max({1}%month_id)
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 !
Hi Ruben
it did not work, i posted an adapted application
thanks
felipe
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?
Try =max({$<month_year_text=,%month_id ={">$(=$(vFrom))<=$(=$(vTo))"}>}month_id)
hi
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.
perfect! thanks
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.