CODEWITHSUNDEEP
qlikview
oharab
oharab

Reputation: 4425

Monthly Tables in QlikView with Missing Months

Is there a way of doing a pivot table style analysis, using months in this instance, but show zeroes for missing months rather than suppressing the field? I'm trying to aggregate my data into monthly totals, but not every category has values for every month, so when filters are applied it will skip months, which can give the wrong impression.

As an example, I have Officers attending incidents of varying incident types several times per day. I'd like to summarise the total number of incidents arrived at per month, by type. This is a fairly easy chart to produce, I'm using a calendar. However, if I I use an officer as a filter and that officer hasn't attended any incidents in that month, rather than showing 0, it just doesn't show that month, which makes sense, but it's not what I want to see.

Upvotes: 1

Views: 3183

Answers (1)

i_saw_drones
i_saw_drones

Reputation: 3506

This behaviour is by design in QlikView as you discovered, if the data is physically missing then QlikView cannot be coaxed into displaying it in a pivot table.

I have never really found any quick fixes to this problem (i.e. via a checkbox or other setting), apart from adding the values into your document's data via the load script. The disadvantage of this is that it adds a lot of redundant values to your document data, and depending on the volume, could cause performance issues in the document.

I took your example and made up a small script that will add the "missing" values to your data. This means that when you do click to select an officer, all months should be present in your pivot table chart. It's not polished and probably not the best or neatest way, but it works (I have used something similar on other projects), plus I am not sure how you have your calendar set up.

Anyway, it might get you thinking in the right direction!

Incidents:
LOAD
    Officer,
    IncidentType,
    date#(AttendanceDate,'dd/MM/yyyy') as AttendanceDate,
    AttendanceCount;
LOAD * INLINE [
    Officer, IncidentType, AttendanceDate, AttendanceCount
    A, A, 01/01/2014, 1
    B, C, 01/01/2014, 1
    C, C, 01/02/2014, 1
    D, C, 01/01/2014, 1
    E, D, 01/01/2014, 1
];

AllCombinations:
NOCONCATENATE 
LOAD DISTINCT
    Officer,
    IncidentType
RESIDENT Incidents;

LEFT JOIN
LOAD DISTINCT
    AttendanceDate,
    0 as AttendanceCount
RESIDENT Incidents;

CONCATENATE (Incidents)
LOAD
    *
RESIDENT AllCombinations;

DROP TABLE AllCombinations;

Calendar:
LOAD DISTINCT
    month(AttendanceDate) as AttendanceMonth,
    AttendanceDate;
LOAD DISTINCT
    AttendanceDate as AttendanceDate
RESIDENT Incidents;

The way it works is that after the initial load (of Incidents), we load the unique combinations of your dimensions except for the date field:

AllCombinations:
NOCONCATENATE 
LOAD DISTINCT
    Officer,
    IncidentType
RESIDENT Incidents;

We then determine all the date values that we wish to add to each entry of the above table and then use a LEFT JOIN (this is a QlikView join rather than SQL) to replicate the entries for each unique value of AttendanceDate:

LEFT JOIN
LOAD DISTINCT
    AttendanceDate,
    0 as AttendanceCount
RESIDENT Incidents;

We then glue this into the original Incidents table and then drop the table.

CONCATENATE (Incidents)
LOAD
    *
RESIDENT AllCombinations;

DROP TABLE AllCombinations;

The final part of the script just creates a calendar table.

Upvotes: 3

Related Questions