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?
