8 Replies Latest reply: Jul 4, 2016 9:00 AM by Joanna Seldon

# Month sort based on selections

Hi

need help with expression for sorting months , based on selection of a filter

I currently have a chart with an expression to calculated from todays date and last 12 month backwards ..so today currently starting at July and ending on June,

sort by expression is  = max(EventDate)

this is perfect for when a person enters the app, and is required

however....

I need to be able to sort the months  if when a user selects a finical year, so the chart months dynamically changes in finical year month order apr to mar.

• ###### Re: Month sort based on selections

Maybe I misunderstand your request, but it doesn't sound that you want to change the sort order, but the range of months to be shown.

I assume you are filtering the range e.g. using set analysis in your expression?

Then you would need to change this part to show the financial year instead of last 12 months, e.g. by using a condition based on GetSelectedCount(FinancialYear).

• ###### Re: Month sort based on selections

Hi

yes sort of, I don't want to change the current sort order

currently today starting at July and ending on June,

but yes if a user selects a finicial year the month order does need to change to april to March.

I need to know how I can use both

= max(EventDate)

and if finical year selected from a filter pane change the sort order dynamically.  month order

I was using

if (CalendarMonthName = 'Apr' , 1 ,

if (CalendarMonthName = 'May' , 2 ,

if (CalendarMonthName = 'Jun' , 3 ,

if (CalendarMonthName = 'Jul' , 4 ,

if (CalendarMonthName = 'Aug' , 5 ,

if (CalendarMonthName = 'Sep' , 6 ,

if (CalendarMonthName= 'Oct' , 7 ,

if (CalendarMonthName = 'Nov' , 8 ,

if (CalendarMonthName= 'Dec' , 9 ,

if (CalendarMonthName = 'Jan' , 10 ,

if (CalendarMonthName = 'Feb' , 11 , 12)))))))))))

to get the month finical month order.

• ###### Re: Month sort based on selections

my sum expression is as follows

• ###### Re: Month sort based on selections

You can do those IF Statements in a Preceding Load like this:

YouTable:

if (CalendarMonthName = 'Apr' , 1 ,

if (CalendarMonthName = 'May' , 2 ,

if (CalendarMonthName = 'Jun' , 3 ,

if (CalendarMonthName = 'Jul' , 4 ,

if (CalendarMonthName = 'Aug' , 5 ,

if (CalendarMonthName = 'Sep' , 6 ,

if (CalendarMonthName= 'Oct' , 7 ,

if (CalendarMonthName = 'Nov' , 8 ,

if (CalendarMonthName= 'Dec' , 9 ,

if (CalendarMonthName = 'Jan' , 10 ,

if (CalendarMonthName = 'Feb' , 11 , 12))))))))))) as FiscalOrder

FROM [yourdatabase]

GROUP BY FiscalOrder ASC;

Hope it helps

Regards,

MB

• ###### Re: Month sort based on selections

Hi

this does help, but need help with the sorting expression

if finical year slected = true then  FiscalOrder

else = max(EventDate)

• ###### Re: Month sort based on selections

Create a conditional based on GetSelectedCount(FIELD):

=If( GetSelectedCount( [finical year] ), Max(FiscalOrder), Max(EventDate) )

• ###### Re: Month sort based on selections

Hi

thank you and

needed to put the months the other order as max EventDate needs to be desc

if (CalendarMonthName = 'Apr' , 12 ,

if (CalendarMonthName = 'May' , 11 ,

if (CalendarMonthName = 'Jun' , 10 ,

if (CalendarMonthName = 'Jul' , 9 ,

if (CalendarMonthName = 'Aug' , 8 ,

if (CalendarMonthName = 'Sep' , 7 ,

if (CalendarMonthName= 'Oct' , 6 ,

if (CalendarMonthName = 'Nov' , 5 ,

if (CalendarMonthName= 'Dec' , 4 ,

if (CalendarMonthName = 'Jan' , 3 ,

if (CalendarMonthName = 'Feb' , 2 , 1))))))))))) as FiscalOrder

• ###### Re: Month sort based on selections

You could something like this:

Let vYearSelected = =If(GetSelectedCount( [finical year] ) = 0, 0, 1)

YouTable:

IF \$(vYearSelected) = 0

if (CalendarMonthName = 'Apr' , 1 ,

if (CalendarMonthName = 'May' , 2 ,

if (CalendarMonthName = 'Jun' , 3 ,

if (CalendarMonthName = 'Jul' , 4 ,

if (CalendarMonthName = 'Aug' , 5 ,

if (CalendarMonthName = 'Sep' , 6 ,

if (CalendarMonthName= 'Oct' , 7 ,

if (CalendarMonthName = 'Nov' , 8 ,

if (CalendarMonthName= 'Dec' , 9 ,

if (CalendarMonthName = 'Jan' , 10 ,

if (CalendarMonthName = 'Feb' , 11 , 12))))))))))) as FiscalOrder

FROM [yourdatabase]

GROUP BY FiscalOrder ASC;

IF \$(vYearSelected) = 1

FROM [yourdatabase]

OORDER BY EventDate DESC;

Hope it helps

Regards,

MB