Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I am struggling with a Prior Term analysis in QlikView. In this scenario, I don't have the information grouped by year or month, I have it by term (In this case is Semester). So what I need is a table in which I can represent the information by term and by department and I need my actuals and my actuals of the prior term.
I am loading my information from the database, I have the column TermKey which is the key for every Term. For example:
TermKey | SourceKey | Description |
1 | 20141 | First Semester of 2014 |
2 | 20142 | Second Semester of 2014 |
3 | 20151 | First Semester of 2015 |
4 | 20152 | Second Semester of 2015 |
5 | 20161 | First Semester of 2016 |
6 | 20162 | Second Semester of 2016 |
So I need the actual value for Second Semester of 2016 (TermKey 6) and Prior term which will be First Semester of 2016 (TermKey 5). The funny part is that I need this to be dynamic so if I select any other term then I need to get the respective prior term information.
I hope I was clear enough. Many thanks in advance for your help.
Regards,
I am not sure whether I understand the question clearly.. Is the problem about getting previous year's value? If so, do you wanna try function, above, in expression?
Perhaps this works for you:
Terms:
LOAD * INLINE [
TermKey, SourceKey, Description
1, 20141, First Semester of 2014
2, 20142, Second Semester of 2014
3, 20151, First Semester of 2015
4, 20152, Second Semester of 2015
5, 20161, First Semester of 2016
6, 20162, Second Semester of 2016
];
LEFT JOIN (Terms)
LOAD
TermKey + 1 as TermKey,
SourceKey as PriorSourceKey,
Description as PriorDescription
RESIDENT
Terms
;
If not, perhaps an As-Of table will do the trick: The As-Of Table
Hi guys,
Thanks for your response. The problem when using Load INLINE is that my fields remain static, so I will have to update them on every new term and I want to avoid that.
Regarding to the Above() function, the problem is that because of the structure of the report, I can´t get the values from the term but for the College (Example below):
Term | First Semester of 2015 | First Semester of 2015 | Second Semester of 2015 |
SIS Course College | Distinct Courses | Prior Term | Distinct Courses |
College A | 286 | - | 636 |
College B | 88 | 286 | 30 |
College C | 40 | 88 | 79 |
College D | 27 | 40 | 9 |
College E | 0 | 27 | 0 |
College F | 0 | 0 | 26 |
No SIS College Mapped | 0 | 0 | 0 |
So I am retrieving correctly the values from the row above but report-level so in College C it is getting values from College B and so on. I think I am close, is there any way to apply this like database level or something like that?
Thank you so much for your time and patience with this concern guys.
The inline load is just an example. I don't have access to your data source so I can't use that.
Hi Gysbert,
Actually that is my real data, but a new term will be added every 6 months, is there any way I can set that LOAD Inline so it gets every new term when added?
No, the inline load is hard-coded. If you want to retrieve the new data then you need to load it from the data source where the new data is entered.