Month sort based on selections

[joeybird]

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.

please help

[swuehl]

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).

[joeybird]

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.

please help

[joeybird]

my sum expression is as follows

Sum({$<[EventDate.CalendarMain.Month]=, [EventDate.CalendarMain.Year]=, [EventDate.CalendarMain.Date]={">=$(=MonthStart(AddMonths(Max(EventDate),-12)))<$(=MonthEnd(Max(EventDate)))"}>} [Sales])

please help

[miguelbraga]

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

YouTable:

LOAD *,

     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

LOAD *

FROM [yourdatabase]

GROUP BY FiscalOrder ASC;

Hope it helps

Regards,

MB

[joeybird]

Hi

this does help, but need help with the sorting expression

if finical year slected = true then  FiscalOrder

else = max(EventDate)

please help

[swuehl · Accepted solution]

Create a conditional based on GetSelectedCount(FIELD):

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

[miguelbraga]

You could something like this:

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

YouTable:

IF $(vYearSelected) = 0

     LOAD *,

          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

     LOAD *

     FROM [yourdatabase]

     GROUP BY FiscalOrder ASC;

IF $(vYearSelected) = 1

     LOAD *

     FROM [yourdatabase]

     OORDER BY EventDate DESC;

Hope it helps

Regards,

MB

[joeybird]

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