Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to have a chart that displays the information sorted in months i.e., i want current month-1 to appear as the first month,current month-2 to appear as the second month and so on upto current month-12 appearing as the last month in the chart.Can anyone help me accomplish this please
month-1, month-2... are your months right? if so, try:
=Dual(MonthField, Num(SubField(MonthField,'-',2)) )
Or,
simply: = Num(SubField(MonthField,'-',2))
This should be your custom sort expression.
I did try the above two approaches by placing the expression given in the expression field of sort tab in the chart but its not working
If your month field is numeric (or dual value like when created with the Month() function) you can use rank(month) as expression for sorting. If it's a string value you'll have to create a numeric month in the script first. If you have a date field then use the monthstart function to create such a field: date(monthstart(MyDate),'MMM') as MyMonth
Data format dependency is there. Please upload a sample with small data.
rank(month) sorts the month as Jan,feb,Mar... dec but i need to sort it as aug(current month-1),jul(current month-2),jun(current month-3) and so on upto september which is (current month-12). I have acheived the same by using the following expression in the sort tab:
=
match(MyMonth,Month(AddMonths(Date(Today()),-1))
,Month(AddMonths(Date(Today()),-2))
,Month(AddMonths(Date(Today()),-3)),Month(AddMonths(Date(Today()),-4)),
Month(AddMonths(Date(Today()),-5)),Month(AddMonths(Date(Today()),-6)),Month(AddMonths(Date(Today()),-7)),Month(AddMonths(Date(Today()),-8)),Month(AddMonths(Date(Today()),-9))
,Month(AddMonths(Date(Today()),-10)),Month(AddMonths(Date(Today()),-11)),Month(AddMonths(Date(Today()),-12)))
But then it would be helpful if anyone could give an altenative method to acheive this as the method that i have been using seems to be more of hard coding
Hi,
PFA containing the application
I have achieved the sorting capability using the expression:
=match(MyMonth,Month(AddMonths(Date(Today()),-1))
,Month(AddMonths(Date(Today()),-2))
,Month(AddMonths(Date(Today()),-3)),Month(AddMonths(Date(Today()),-4)),
Month(AddMonths(Date(Today()),-5)),Month(AddMonths(Date(Today()),-6)),Month(AddMonths(Date(Today()),-7)),Month(AddMonths(Date(Today()),-8)),Month(AddMonths(Date(Today()),-9))
,Month(AddMonths(Date(Today()),-10)),Month(AddMonths(Date(Today()),-11)),Month(AddMonths(Date(Today()),-12)))
But,can you please suggest an alternative method for the same as the one that I have been using seems more like hard coding
Thanks & Regards
Anushree Shetty,
+91 9535028666
Hi Anushree,
There would be many other ways, this one just clicked now. Used expression:
=If(Month(MyMonth)-Month(Today())<0, (Month(MyMonth)-Month(Today())+12),(Month(MyMonth)-Month(Today())))
PFA.