I am trying to establish the correct combination of set analysis and/or scripted dimensions to achieve a distinct of users (IDs) who have had a specific service type within a timeframe year-to-date e.g for Month = Apr, should count users with that service type at any time between between 01/04 and 30/04, if Month=July, should count users with that service type at any time between 01/04 and 31/07
The data model (I inherited and can't change ) has a 'Period' table where Months do not correspond to calendar months but 4 or 5 week invoice periods e.g.
Month Start End
April 01/04 28/04
May 29/04 02/06
etc.
As I want my calculation to relate to calendar months, I have added some fields to this table via the script so that each month has a month start, month end, year start and year end.
So, the 'cumulative' count expression I have is :
Count({$<[Care package]= ,[Element Type]= , [Service Group]= ,[Service Type]={'Direct payment','Direct payment (old)'},[Service Start Date]={"<=$([PIReportingMonthEnd])"},
[Service End Date]={">$([PIReportingYearStart])"}>
+<[Care package]= ,[Element Type]= , [Service Group]= ,[Service Type]={'Direct payment','Direct payment (old)'},[Service Start Date]={"<=$([PIReportingMonthEnd])"},
[Current Element]={'Yes'}>}
DISTINCT [Client ID])
To explain, PIReportingMonthEnd is a field which should return the calendar month end for each month and PIReportingYearStart = year start date for a particular month. 'Current Element' is a boolean field which = 'Yes' if the service has no end date
I am trying to get a distinct count of users with a service of type = 'Direct payment' or 'Direct payment (old)' between 1st April (of the selected year) and Month End date (of month selected)
even though my expression has criteria filtering on the start and end dates of the service, to check that the service is live in the YTD period, it still returns a count which relates to each month individually ??
Can anyone help ?
