Inline sort based on the latest month

[#1]

Hi,

I had some of the help in creating the below inline for a dashboard, but I need something to work it around in a diffrent way (i.e. if we are in april 2012 I want it to show in the chart as the last month and i am not shure how to amend this inline to give me the correct rank?

Load * inline [

ABC_Mth ,Month_No

Jan,1

Feb,2

Mar,3

Apr,4

May,5

Jun,6

Jul,7

Aug,8

Sep,9

Oct,10

Nov,11

Dec,12

];

I would apprecite if I can get some help.

example of what i would like to show:

if(Date(today(),'MMM'),1

if(Date(addmonths(today(),-1),'MMM'),2

so on...

Redha

[CELAMBARASAN]

Hi,

     You mean you need to show the latest month as First?

Ex:this month is April then you need order like

Apr,4

May,5

Jun,6

Jul,7

Aug,8

Sep,9

Oct,10

Nov,11

Dec,12

Jan,1

Feb,2

Mar,3

Am i right?

Celambarasan

[#3]

Hi,

You are right, but I believe I haven't really explained properly.

as my chart works on 13 months rolling I need it to show always the month we are in like below:

if in a month time we are in May I need it to show as the last month in the chart is May and going back ward to 13 month.

If this helps, here is my expression in my chart at the moment:

avg(aggr(sum(if(ABC_Mth_Yr>=AddMonths(Today(),-13) and ABC_Mth_Yr<=AddMonths(Today(),0),NET_SALE)),ISSUE_DATE))

so, wherever is ABC_Mth_Yr<=AddMonths(Today(),0) needs to be the last point on the chart.

Regards,

[CELAMBARASAN]

Hi,

     Use MonthName(ABC_Mth_Yr) as Dimension

     In which you can sort easily.

Celambarasan

[#5]

Hi,

You are right, but the problem I've got is I have two expressions to add in as below:

avg(aggr(sum(if(ABC_Mth_Yr>=AddMonths(Today(),-13) and ABC_Mth_Yr<=AddMonths(Today(),0),NET_SALE)),ISSUE_DATE))

and the other is:

avg(aggr(sum(if(ABC_Mth_Yr>=AddMonths(Today(),-26) and ABC_Mth_Yr<=AddMonths(Today(),-13),NET_SALE)),ISSUE_DATE))

and that wont show properly in one chart.

Redha

[CELAMBARASAN]

Hi,

     Ok then try like this

LOAD *,if(Num(MonthNo)>=Month(Today()),MonthNo,MonthNo*100) as MonthSort INLINE

[Month, MonthNo

    Jan, 1

    Feb, 2

    Mar, 3

    Apr, 4

    May, 5

    June, 6

    July, 7

    Aug, 8

    Sep, 9

    Oct, 10

    Nov, 11

    Dec, 12

];

Use Sort by expression as MonthSort

Celambarasan