Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

joeybird
Contributor II

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.

please help

1 Solution

Accepted Solutions
MVP
MVP

Re: Month sort based on selections

Create a conditional based on GetSelectedCount(FIELD):

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

8 Replies
MVP
MVP

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

joeybird
Contributor II

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.

please help

joeybird
Contributor II

Re: Month sort based on selections

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
Valued Contributor III

Re: Month sort based on selections

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
Contributor II

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)

please help

MVP
MVP

Re: Month sort based on selections

Create a conditional based on GetSelectedCount(FIELD):

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

miguelbraga
Valued Contributor III

Re: Month sort based on selections

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
Contributor II

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