Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

jcampbell474
Contributor III

Populate missing data points with 0 (zero)

I was able to create a QV app that enables us to view facilities that are open or closed during a specified date range, thanks to Sunny T and Clever Anjos.  Link to thread: https://community.qlikview.com/thread/228007

Using the same application, I need to add the number of quotes each agency produced.  I thought that by creating a date value per interval, I could at least see nulls.  The best I can do is see agencies that have at least one interval with a value.  If’s it’s zero across date intervals, then they’re not listed (fyi - nulls and/or zero values are not suppressed).  Feels like the value of 0 (zero) should be added along with the date value.

Here is a condensed version of what I’m loading right now.

Data:
LOAD
id,
YearMonth,
value

From QVD;

Agency:
LOAD
id,
agname,

OpenFiscalYearMonth,

CloseFiscalYearMonth

From QVD;

Agencies_x_:
Load
id,
(
OpenFiscalYearMonth + IterNo() -1) as RefDate
Resident Agency
While IterNo() <= CloseFiscalYearMonth - OpenFiscalYearMonth + 1;

Master_Calendar:

LOAD Distinct
FiscalYearMonth as RefDate,
FiscalMonthOfYear as Month,
FiscalYear as Year,
From QVD ;

Does something need to be added to the Agencies_x_ table to populate months where no value is present? I added “0 as filler”, then in my expression, used value+filler.  But, it didn’t work.

Maybe this is the wrong approach.  Can anyone please tell me what I’m doing wrong of if I should just try something different?