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

johnny2005uk — Fri, 27 Apr 2018 21:47:50 GMT

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.

Date	Total
3/15/18	1,651
3/16/18	1,141
3/17/18	1,135
3/18/18	1,619
3/19/18	1,148
3/20/18	1,668
3/21/18	1,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?

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

petter — Fri, 27 Apr 2018 22:53:18 GMT

I think this might be a valuable read for you:

The As-Of Table

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

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

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