topic Floating Qtrs? in QlikView

Floating Qtrs?

Tue, 12 Nov 2013 02:47:47 GMT

Hi All,

I'm trying to analyse machine readings (keep it simple and say photo copiers)

Each client has a variable contract start date.

We want to analyse each clients usage by Q1,Q2,Q3,Q4.

For each machine we want to see the latest readings in each quarter from a list of readings.

So my 2 source tables are

Client Contract Start

A 01/04/2013

B 01/06/2013

Client Machine Reading Date

A 001 500 01/04/2013

A 001 650 30/04/2013

A 001 800 10/07/2013

A 001 1000 30/07/2013

B 001 500 01/06/2013

B 001 650 30/06/2013

B 001 800 10/09/2013

B 001 1000 30/09/2013

My output I would like (report works for one client at a time in essnce)

Client Machine Qtr 1 Qtr2 Usage

01/04/13 01/07/13

A 001 650 1000 350

Client Machine Qtr 1 Qtr2 Usage

01/06/13 01/09/13

B 001 650 1000 350

I've tried set analysis, flagging the data, building a calendar from the dates but to no avail.

Any help appreciated.

Thanks

Steve

Re: Floating Qtrs?

Anonymous — Tue, 12 Nov 2013 03:14:23 GMT

Maybe create a False Date Field where you subtract the Date Field from the Start Field less one month. This would always have the first month be January so regardless of the start date their first reading will be in Q1.

Re: Floating Qtrs?

kaushiknsolanki — Tue, 12 Nov 2013 06:30:52 GMT

Hi,

Have a look at the application attached.

Regards,

Kaushik Solanki

Re: Floating Qtrs?

Tue, 12 Nov 2013 08:20:59 GMT

Hi Kaushik,

I cannot see anythiing attached?

Regards

Steve

Re: Floating Qtrs?

kaushiknsolanki — Tue, 12 Nov 2013 08:25:22 GMT

Hi,

Attachment is der.

Regards,

Kaushik Solanki

Re: Floating Qtrs?

Tue, 12 Nov 2013 10:45:15 GMT

Hi Kaushik,

Not quite what I was after, its close.

However, the base date for the quarter calculation is based on the contract start date which varies for each client not the standard 1st Jan. So in my data set, July for Client A is Qtr 2, whilst July for Client B is Qtr 1.

Also with your expression here being max(reading) I'm not sure how i would get to the usage figure which is the variance between the two quarters.

Thanks in advance.

Steve