Rolling 52 weeks - Stumped

[helen_pip]

Hello

Thank you to anyone who has helped me previously on this issue, but am really struggling to get my table to show a rolling 52 week period

I have the expression ready which is currently looking at this years  FY data and - last years FY data.

But now instead of this years V's last year, I need to show a rolling 52 week period

Could someone kindly help me write this, or tell me exactly what I need to have in order for this to work I.e. Do I need to look at Week Number, and if so how do I calculate the financial year Apr-Mar

Any help or advice is greatly appreciated

=(Count({<YEAR = {$(=Max(YEAR))}>}LOCAL_PATIENT_IDENTIFIER)-Count({<YEAR = {$(=Max(YEAR)-1)}>}LOCAL_PATIENT_IDENTIFIER))

Thanks

Helen

[juleshartley]

Hi Helen,

We might need a few more details to give you the best approach -

Are you using dates or just week numbers? Do you want everything from a year ago from the month end selected or week selected or day selected?

If your granularity is by day then I would set a variable to = a year ago from date selected [eg. vYear_Last = Date(AddYears(vDate,-1),'DD/MM/YYYY')] where vDate = the date date selected (or month end etc).

Then use set analysis to say give me everything less than the date selected and more than vYear_Last.

If your data is linked to the calendar table then you'll need to remove those selections first

eg.

=

          Count(

          {

          <

          YEAR =

          ,WEEK =

          ,DATE={"<=$(vDate)"}

          ,DATE={">$(vYear_Last)"}

          >

          }

          LOCAL_PATIENT_IDENTIFIER

          )

[helen_pip]

Hello

Thank you for your help

My data set starts from  01/04/2012 and dates through to last month

I am currently using dates and YEAR is calculated off a piece of Oracle SQL:

CASE WHEN TO_NUMBER (TO_CHAR (date, 'MM')) >= 4 THEN TO_NUMBER (TO_CHAR (date, 'YYYY'))

WHEN TO_NUMBER (TO_CHAR (date, 'MM')) < 4 THEN TO_NUMBER (TO_CHAR (date, 'YYYY')) - 1 END AS Year

I do however have week available ot me

Date

(Weekstart([REFERRAL_DATE]), 'DD-MM-YYYY') AS OutPatients_Week

Or

Right

(WeekName([REFERRAL_DATE]),2) as week_number

My data is granulated to person level and numerous people can have the same date

Thanks you for your help

[juleshartley]

Ok, but I'm not clear if you want a true 'rolling year' (ie. 365 days) or you want to go back to the beginning of the financial year, ie. 1st April..? which is year-to-date rather than rolling year