Floating Qtrs?

[#1]

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

[Anonymous]

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.

[kaushiknsolanki]

Hi,

     Have a look at the application attached.

Regards,

Kaushik Solanki

[#4]

Hi Kaushik,

I cannot see anythiing attached?

Regards

Steve

[kaushiknsolanki]

Hi,

     Attachment is der.

Regards,

Kaushik Solanki

[#6]

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