topic Re: Prior TERM Analysis in QlikView in QlikView

Prior TERM Analysis in QlikView

Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Prior TERM Analysis in QlikView

Fri, 22 Jul 2016 07:40:33 GMT

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?

Re: Prior TERM Analysis in QlikView

Gysbert_Wassenaar — Fri, 22 Jul 2016 07:57:15 GMT

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

Re: Prior TERM Analysis in QlikView

Fri, 22 Jul 2016 15:19:07 GMT

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.

Re: Prior TERM Analysis in QlikView

Gysbert_Wassenaar — Fri, 22 Jul 2016 15:27:03 GMT

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

Re: Prior TERM Analysis in QlikView

Fri, 22 Jul 2016 15:44:26 GMT

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?

Re: Prior TERM Analysis in QlikView

Gysbert_Wassenaar — Fri, 22 Jul 2016 15:52:25 GMT

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.