Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have reports for roughly 125 offices.
In my qlikview report I have one table with information about all 125 offices. This information is static and rarely changes.
I have a second Fact table that comes from weekly reports I receive.
The issue I am having is that the reports come only with the offices that have data to share. I need to populate a zero for the offices not listed.
My office list are driven by office number. It is not a straight 1 through 125 but there are 125 numbers that each refer to the office.
The report I get is two columns: Office number and the another number field but this report only has lines for the offices that have a number to report. If it is Zero then it doesn't appear on the report. Is there a way to populate a Zero for these missing offices so an average would be a true average rather than an average of when they report a number?
Thanks in advance for the help,
Have a look at
Generating Missing Data In QlikView
Another option would be to not use Avg() function but a division of your aggregated numbers by the selected number of weeks (or any other number that would return you the corret denominator)
=Sum( MeasureField) / Count(TOTAL DISTINCT Week)
Perhaps like this?
[This Week's Reports]:
LOAD
[Office]
,[Office] as [Reporting Office]
,[Number]
FROM somewhere
;
CONCATENATE [This Week's Reports]
LOAD
[Office]
,0 as [Number]
RESIDENT [Offices]
WHERE not exists([Reporting Office],[Office])
;
DROP FIELD [Reporting Office];