Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
johnny2005uk
Partner - Contributor II
Partner - Contributor II

Set Modifiers (field against field) - Point in time reporting

So I'm totally aware of the resource issues associated with field on field set analysis, but I'm stuck overcoming a scripting issue and wondered if anyone had any suggestions for the following: -

We have a very basic script for point in time reporting in SQL

DECLARE @AS_OF_DATE DATE = '3/15/2018'

SELECT COUNT(DISTINCT NO_SID) as Total

@AS_OF_DATE as Date

FROM DIM

WHERE INQUIRY_DT IS NOT NULL

       AND STATUS_CD = 1

       AND @AS_OF_DATE BETWEEN ROW_START_DAY AND ROW_END_DAY

GROUP BY Date;


Returning one row of results, then I accounted for additional dates by passing through a min/max generate calendar to then loop through each of those dates, creating a history of results e.g.


DateTotal
3/15/181,651
3/16/181,141
3/17/181,135
3/18/181,619
3/19/181,148
3/20/181,668
3/21/181,789

But... I don't want to use the loop, since it's very case specific and our data model has multiple metrics with different dates, which would require a loop process creating for each.

I can't find a way with set analysis to make this work, but essentially something along the following in theory would work.

I created a date island not associated to the model, which would act as the date reference. The set analysis expression I imagine would start to look like the following: -

sum({$<CalendarDate >= [ROW_START_DAY], CalendarDate <= [ROW_END_DAY]

, [INQUIRY_DT] = {'>0'}, [STATUS_CD] = {'1'}>} 1)

I know my modifiers are incorrect between date fields, but used them to illustrate what was in my mind. Does anyone have any approach that I've not considered? Interval match somehow?




1 Reply
petter
Partner - Champion III
Partner - Champion III

I think this might be a valuable read for you:

   The As-Of Table