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

    Month sort based on selections

    Joanna Seldon

      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

        • Re: Month sort based on selections
          Stefan Wühl

          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
              Joanna Seldon

              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

                • Re: Month sort based on selections
                  Joanna Seldon

                  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
                      Miguel Braga

                      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

                        • Re: Month sort based on selections
                          Joanna Seldon

                          Hi

                           

                          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
                              Stefan Wühl

                              Create a conditional based on GetSelectedCount(FIELD):

                               

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

                                • Re: Month sort based on selections
                                  Joanna Seldon

                                  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
                                  Miguel Braga

                                  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