topic Re: Dates and historic data in QlikView

Dates and historic data

alisonpwallis — Wed, 05 Mar 2014 10:01:43 GMT

Dear All,

I have some historic data that I need to use to compare to current data. This data is a subset of the applications to some degree programs. Each applicant can apply to more than one degree in more than one year. The data is for 2 consecutive academic years - 2012-13 and 2013-14. Each application has a status that changes during the year - so the applicant applies and gets a status, then they might get an offer of a place and the status changes, then they accept or reject the offer so their status changes again. An individual application will therefore have several records - one for each status.

What I want to be able to do is recreate the situation as of a particular date in time. So for example I want to be able to see how many applicants had a status of 'C' on 5 March 2013 so I can compare that with how many have a status of 'C' on 5 March 2012.

Does anyone have any ideas about how I might go about this?

I've attached a sample qvw file with the data so you can hopefully see what I mean. Please let me know if you need any more information.

Thanks very much for your help

Alison

Re: Dates and historic data

Nicole-Smith — Wed, 05 Mar 2014 16:20:13 GMT

Have a look at the line chart in the attached. It should give you something to go off of.

Re: Dates and historic data

alisonpwallis — Wed, 05 Mar 2014 16:48:50 GMT

Hi Nicole

Thank you for this - it is really useful! Is it possible to create a table chart in QV that you can then turn into a QVD and export to use in another qvw?

Also, I have a lot of associated data by which I would want to filter the various numbers in the report - for example, the degree the applicants are applying to, the department that degree belongs to, the country from which the applicant is coming. Is there a way of incorporating this? The data is joined by the applicant number and the ID to all the other data.

Thanks again for your help

Alison

Re: Dates and historic data

Nicole-Smith — Wed, 05 Mar 2014 17:25:26 GMT

Alison Wallis wrote:

Thank you for this - it is really useful! Is it possible to create a table chart in QV that you can then turn into a QVD and export to use in another qvw?

I would suggest you export the data in the script into a QVD using the STORE method:

Alison Wallis wrote:

Also, I have a lot of associated data by which I would want to filter the various numbers in the report - for example, the degree the applicants are applying to, the department that degree belongs to, the country from which the applicant is coming. Is there a way of incorporating this? The data is joined by the applicant number and the ID to all the other data.

If your data is all linked, you should be able to just create list boxes or multiboxes with the filters that you want, and it should filter the chart accordingly. This is just how QlikView works

Re: Dates and historic data

alisonpwallis — Mon, 10 Mar 2014 11:54:02 GMT

Hi Nicole

Thanks again for your help.

My problem is that I want to be able to select a particular date for the data - and to find out what the applicants status was on that date whether or not it was a date on which the status changed. So for example if I am looking today I can see there are 100 applicants with a status of C and 200 with a status of U, 300 with a status of R and 50 with a status of W. I want to know for 10th March 2013 what the numbers of students were. BUT what I want to be able to do is to filter on the data in the same way that I can for today. So I think effectively I want to recreate todays dashboard for every day for the past two years (if only we had a data warehouse instead this wouldn't be a problem). However, even if I can do this I don't know how to get the date part so that I get the status for that particular date rather than the final status (these are stored in separate places). I think perhaps I need to get someone to come in as it's really hard to describe....

As regards the store function I was thinking rather whether you could take the data from the dashboard rather than the load script itself. So if I created a table in the qvw that I wanted to export into a qvd (like I would export to excel). Is that possible?

Thanks again for your help.

Alison

Re: Dates and historic data

Nicole-Smith — Mon, 10 Mar 2014 12:50:22 GMT

Alison Wallis wrote:

My problem is that I want to be able to select a particular date for the data - and to find out what the applicants status was on that date whether or not it was a date on which the status changed.

Since you only have the dates that the status changed, I think you may need to do something like this to get the status on the rest of the dates: http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field

Alison Wallis wrote:

As regards the store function I was thinking rather whether you could take the data from the dashboard rather than the load script itself. So if I created a table in the qvw that I wanted to export into a qvd (like I would export to excel). Is that possible?

Maybe with a macro? I can't say for sure. I know it's not built-in functionality.