Answered

Dynamic SortByColumns

(0) Share

Report

Posted on by rmc

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!

All responses (3)

Answers (1)

PowerAddict 7,314 Most Valuable Professional on at

Like (0)

Report

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
rmc 51 on at

Like (1)

Report

Re: Dynamic SortByColumns

Thanks Hardit!

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

Mark
Verified answer

PowerAddict 7,314 Most Valuable Professional on at

Like (1)

Report
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