Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zakpullen
Creator
Creator

Return text value from previous month in a straight table

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?

tbl2.JPG

Many thanks

Labels (1)
1 Solution

Accepted Solutions
Or
MVP
MVP

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:

Or_0-1654106980481.png

 

 

View solution in original post

8 Replies
Or
MVP
MVP

Have you tried using Above(Department)?

 

zakpullen
Creator
Creator
Author

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.

Or
MVP
MVP

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
];

Or_0-1654084493920.png

 

 

zakpullen
Creator
Creator
Author

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

Or
MVP
MVP

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:

Or_0-1654106980481.png

 

 

zakpullen
Creator
Creator
Author

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.

date.JPG

I'm baffled!

 

Or
MVP
MVP

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.

zakpullen
Creator
Creator
Author

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.