Hello,
I have a crosstab datawindow that shows year-over-year data. The columns (values) are weekly totals for X number of weeks where X must be less than 52 so I can show multiple years with the same date range. The heading @end_week shows the month and day that the week ended.
For example if today is January 8, 2024, and the user wants to see the last 5 weeks for the previous 2 years, the report will show 5 columns: 12_11, 12-18, 12-25, 01-01, 01-08 with 2 rows: 2023 and 2024.
My problem is that Powerbuilder sorts the columns alphabetically: 01-01, 01-08, 12-11, 12-18, 12-25 even when I retrieve the data in chronological order. Is there a way to override this and sort the columns differently?
If there is no way to override it, I could possibly change the retrieved data as "(1) 12-11, (2) 12-18, (3) 12-25, (4) 01-01, (5) 01-08 but I prefer not to litter the report with extra data the user doesn't want to see.
Thank you,
Steven Watkins
I don't believe the sort algorithms sort the columns in the order they are supplied. I supplied the data in chronological order and Powerbuilder rearranged the columns in alphabetical order.
Thank you for your reply. I can preface the column text with a year for a report that is not Year over Year. However I can't do that for a Year over Year report because the columns represent the month and day for any year and the years are listed as rows in the report.
One alternative is to provide a fake year for the columns (example 1900-12-11, 1900-12-18,.... ) so that the column would be a date datatype rather than a string - and Powerbuilder could sort chronologically that way. I know my customers will not like that though.
I tried the technique in my previous post (Wk01 12-11, Wk02 12-18, ...... , Wk05 01-08 and it worked. That is the best I can do for now but it seems like there should be a way to sort the columns in a different order rather than alphabetical order.
Just had a new idea as I typed this. Rather than show 01-01, 01-15, show data as 1-01, 1-15, etc.
Does 12-25 come before 1-01 alphabetically? If so I can take out the Wk01, etc text. Will give it a try.
Thank you again for getting back to me.
I have a perfect solution and it actually solves another issue also. If there are two years as in my example, I simply show the end year (2023 and 2024) rather than year ranges (2022-2023, 2023-2024).
Now if the columns are 12-18, 12-25, 01-01, 01-08, Powerbuilder sorts the columns alphabetically as 01-01, 01-08, 12-18, 12-25, which is not chronological as I want it. But if I put an asterisk in front of 12, Powerbuilder sorts the data as alphabetically as *12-18, *12-25, 01-01, 01-08 which is now chronological as I want. Now I can put in a footnote saying that the astreisk means it applies to the previous year, not the year shown under the End Year column.
Thank you,
Steve