Prior TERM Analysis in QlikView

[#1]

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,

[#2]

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?

[Gysbert_Wassenaar]

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

[#4]

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.

[Gysbert_Wassenaar]

The inline load is just an example. I don't have access to your data source so I can't use that.

[#6]

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?

[Gysbert_Wassenaar]

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.