Calculate quarterly figures from cumulative data

[gemma-the-husky]

out of interest, i would try this, based on the useful and powerful (but little known) partition function.

- include a (it will be simple) function that evaluates the elapsed months, (or quarters) to give a whole number for each row of data

- then try the partition function, based on a gap of 3 (using months as above), to analyse the data.

this will split your data into 3 month blocks - which may help you get where you need to be.

 

[scubadiver007]

Might be a step closer. Since every row is unique to the fieldname and practice I have created an index that will return sequential numbers irrespective of what quarters are uploaded so eg. Q2 and Q4 will return 1 and 2 or Q1, Q2 and Q4 will return 1,2 and 3.

SELECT s.quarternum, s.yearnum, s.fieldname, s.gppracticecode,
(Select Count(*) from data Where data.fieldname = S.fieldname and data.gppracticecode = s.gppracticecode and data.quarternum <= s.quarternum) AS Index

FROM Data as s;

The record with an index of 1 retains the data but subsequent records is a subtraction.

 

[vbaInet]

See attached!

 

[scubadiver007]

Thanks very much for your help. The first records are zero so changed the code slightly so it returns the actual figure.

How can the query be changed so I have numbers instead of text?

 

[vbaInet]

Cool!

Does it make a difference? Do you want to use that number in a calculation?

 

[scubadiver007]

I have multiplied it by a number and it works ok. I need the differences to calculate payments.

All I need to do now is dissect the code to understand it.

 

[vbaInet]

The SELECT part is similar to a DLookup() function but it's faster than the DLookup. It's basically an inline subquery.

If you want to return a number type, you can do:

IIF(IsNumber([[COLOR=Blue]CalculatedField[/COLOR]]), Val([[COLOR=Blue]CalculatedField[/COLOR]]), Null)

 

[scubadiver007]

I changed the query to this to simplify it and it works.

(SELECT TOP 1 T.[numberofpatients] FROM data AS T
WHERE T.gppracticecode = [data].gppracticecode
AND T.[FieldName] = data.[fieldname]
AND T.[yearnum] = data.[yearnum]
AND ((T.[quarternum]) - 1) < (([data].[quarternum]) - 1)