topic Re: Month sort based on selections in App Development

Month sort based on selections

joeybird — Fri, 01 Jul 2016 09:58:46 GMT

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

Re: Month sort based on selections

swuehl — Fri, 01 Jul 2016 10:03:20 GMT

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

joeybird — Fri, 01 Jul 2016 10:09:19 GMT

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

Re: Month sort based on selections

joeybird — Fri, 01 Jul 2016 10:13:58 GMT

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

Re: Month sort based on selections

miguelbraga — Fri, 01 Jul 2016 10:35:38 GMT

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,

Re: Month sort based on selections

joeybird — Fri, 01 Jul 2016 12:33:11 GMT

this does help, but need help with the sorting expression

if finical year slected = true then FiscalOrder

else = max(EventDate)

please help

Re: Month sort based on selections

swuehl — Fri, 01 Jul 2016 12:53:12 GMT

Create a conditional based on GetSelectedCount(FIELD):

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

Re: Month sort based on selections

miguelbraga — Fri, 01 Jul 2016 15:20:14 GMT

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,

Re: Month sort based on selections

joeybird — Mon, 04 Jul 2016 12:59:45 GMT

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