cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rmc
Frequent Visitor

Dynamic SortByColumns

I have a table of sales by month number and want to display that as trailing 12 months eg: If we are in April (month number 4) the display sales staring month 4 (April) thru month 3 (March).

 

I put all the format strings in another table SortText by month and dynamically lookup that string using

 

LookUp(SortText,MonthNumber=Month(Now()),SortOrderMonths) which returns [ 4,5,6,7,8,9,10,11,12,1,2,3 ]

 

this statement does not work and no error given

SortByColumns(SalesTable, "MonthNumber", LookUp(SortText,MonthNumber=Month(Now()),SortOrderMonths))

 

If you hardcode it like this
SortByColumns(SalesTable, "MonthNumber",[ 4,5,6,7,8,9,10,11,12,1,2,3 ])

then it works perfectly!

If anyone has any ideas, I can't see why it is not working.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Dynamic SortByColumns

The problem is when you are doing a LookUp using the table that has your sortorders, it returns a text and that's why it doesn't work. 

 

You might want to try something like this:

SortByColumns(SalesTable, "MonthNumber", Switch(Month,4, [ 4,5,6,7,8,9,10,11,12,1,2,3 ], 5, [5,6,7,8,9,10,11,12,1,2,3,4))

 

Let me know if this helps. 

 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

 

Thanks!

Hardit Bhatia

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Dynamic SortByColumns

The problem is when you are doing a LookUp using the table that has your sortorders, it returns a text and that's why it doesn't work. 

 

You might want to try something like this:

SortByColumns(SalesTable, "MonthNumber", Switch(Month,4, [ 4,5,6,7,8,9,10,11,12,1,2,3 ], 5, [5,6,7,8,9,10,11,12,1,2,3,4))

 

Let me know if this helps. 

 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

 

Thanks!

Hardit Bhatia

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

View solution in original post

rmc
Frequent Visitor

Re: Dynamic SortByColumns

Thanks Hardit!

 

Worked perfectly. Would never of thought to use Switch statement here.

 

Mark

Super User
Super User

Re: Dynamic SortByColumns

Glad I could help! Good luck with the rest of the app! Feel free to reach out in case of any questions!

 

Thanks!

Hardit Bhatia

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Power Platform 2020 release wave 1 plan

Power Platform 2020 release wave 1 plan

Features releasing from April 2020 through September 2020

Top Solution Authors
Top Kudoed Authors
Users online (5,093)