Set Expression for Missing Data

[Deputy_Dawg]

I've recently built a compliance app to monitor the monthly submissions from hundreds of institutions. Each month they should send me data but oftentimes don't. So my app looks at the last five years and tries to identify missing submissions. I did this with SQL in the load editor however it doesn't discriminate from when an institution opened or closed. If that event occurs during the reporting period, the data owed is overstated.
So I figured I would neatly tailor this within Qlik using a set expression, rather than edit the unexpectedly complex SQL (see below). DATAHOLE_DT is the missing month, in the form of date and the end of the month e.g. 28/2/2023 and are unique for each institution. 

Count({$<DATAHOLE_DT={">=$(=[OPEN_DT])<=$(=[CLOSE_DT])"} >} DATAHOLE_DT)

Alas it does not work and I don't seem to be able to tweak it. Any help appreciated.

[marcus_sommer · Accepted solution]

The data-structure and the view-requirements aren't quite clear but it's very likely that your approach couldn't work. A set analysis worked like a selection which means you couldn't use it for row-level conditions and as far a you use a $-sign expansion you will create an adhoc-variable which has only a single value and could be therefore also not applied against a row-level.

Further it's quite difficult to impossible to show any data against NULL respectively NOTHING. You will always need a dimensionality for it and the most simple way is often to populate the missing data, maybe with something like:

temp1: load distinct Institutions from X; join(t) load distinct Periods from Y; 
temp2: load *, Institutions & '|' & Periods as ExistsKey resident temp1;

concatenate(facts) load *, 0 as Value resident temp2 where not exists(ExistsKey);

[Deputy_Dawg]

Thank you Marcus, I appreciate your explanation and the fact that my approach wasn't going to get me over the line. 

Your proposed load script looks promising, I will experiment with it and hopefully get it to work.

Thanks again for helping out!