topic Re: Return text value from previous month in a straight table in App Development

Return text value from previous month in a straight table

zakpullen — Wed, 01 Jun 2022 11:35:48 GMT

Hi,

Staff data is stored on a month by month basis. I'd like to show in a straight table an employee's current department (simple), but also the one they were in last month to identify changes. I'm not sure if this is best handled in the script or the straight table.

MonthYear is part of the mastercalendar, and used in other calculations spanning time.

A simplified version of the staff data is below. How would I show the employee department for Mar 2022, alongside the Apr 2022 department in a straight table?

Many thanks

Re: Return text value from previous month in a straight table

Or — Wed, 01 Jun 2022 11:38:27 GMT

Have you tried using Above(Department)?

Re: Return text value from previous month in a straight table

zakpullen — Wed, 01 Jun 2022 11:43:40 GMT

Thanks for your response.

I just tried that in the straight table, and where it returns values, they are random in relation to the employee.

Re: Return text value from previous month in a straight table

Or — Wed, 01 Jun 2022 11:55:15 GMT

If your sort order is month --> employee, then this will indeed not work, as the row above the current one might be an entirely different employee. If your sort order is employee --> month, it should work correctly:

Load * INLINE [
MonthYear, Employee Number, Department
2022 03, 9999, Dep1
2022 04, 9999, Dep2
2022 05, 9999, Dep2
2022 06, 9999, Dep3
2022 03, 4444, Dep4
];

Re: Return text value from previous month in a straight table

zakpullen — Wed, 01 Jun 2022 12:07:37 GMT

The straight table should only show employees in the current month, so the Above function wouldn't work in the straight table?

Re: Return text value from previous month in a straight table

Or — Wed, 01 Jun 2022 18:10:10 GMT

By definition it would not in this case, but if that's what you're doing, it's actually an entirely different situation and a different approach to a solution. In this case I adjusted the months to be actual dates, so I could use date functions on them:

Only({< MonthYear = {"$(=Date(AddMonths(MonthYear,-1),'YYYYMM'))" } >} Department)

Load date#(MonthYear,'YYYYMM') as MonthYear, [Employee Number], [Department] INLINE [
MonthYear, Employee Number, Department
202203, 9999, Dep1
202204, 9999, Dep2
202203, 4444, Dep4
202204, 4444, Dep5
];

After selecting 202204:

Re: Return text value from previous month in a straight table

zakpullen — Tue, 07 Jun 2022 10:26:16 GMT

Thanks

I was able to replicate your example in a separate qv app, but applying it to the dataset I'm using is proving trickier.

Instead of using MonthYear, I created a new field called SIPDT. It uses the same load expression as yours, (date#(PeriodDate,'YYYYMM') as SIPDT), so should work.

The problem arises when this is used in the formula you provided, specifically this section - Date(AddMonths(SIPDT,-1),'YYYYMM')

Below shows dates, the correct result when formatted as a number, and the numbers actually being produced. A 5 digit number becomes a 6 digit number, but the first 3 digits correspond correctly.

Below the table is an image of the full expression in a straight table.

I'm baffled!

Re: Return text value from previous month in a straight table

Or — Tue, 07 Jun 2022 13:04:12 GMT

If you apply num() to your date field, you should get the corresponding date value, which should indeed by the "correct result" numbers you're getting. However, in your case you seem to have formatted the date as DD/MM/YYYY, so you'd need to have the set analysis syntax match that. This won't solve the issue of the results being incorrect, though - but I can't really see what the problem is without access to the data, so if you'd like me to look at it I'll need a sample app.

Re: Return text value from previous month in a straight table

zakpullen — Wed, 08 Jun 2022 10:59:58 GMT

The DD/MM/YYYY was just to indicate actual months for the other 2 columns, not part of the app. I've made some progress integrating, I just need to work out how to integrate it with the master calendar so that it responds to listboxes based on financial years and months.

Many thanks for your help.