Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.


Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Drilldown / aggregation by multiple date fields


I havent needed to do a model like this before so am unsure of a good way to approach it.

Worked example QVW - MultidatesTest.qvw - below with generic data. I have a autogenerated script calendar table, with 2 data tables both of which contain a date field, primary key is the OrderNo.

2QlikView x64 - [X50 DEVMultidatesTest.png

I was hoping to create a single calendar and then provide aggregation by month so when I click on say Jun it shows me all date fields that fall into that dimension.

In my case it works that way with the calendar table, but clearly because there is no common key the orderdate and deliverydate fields remain unaffected by such filtering.

I also realise that in many instances OrderDate and DeliveryDate don't fall into the same MonthName, so then I suppose one needs to choose one or the other field to aggregate on and ignore the other.

1X50 DEVMultidatesTest.png

I realise I can rename one of the non calendar date fields to the same as the CalendarDate and then that will work, but then I lose the identity of that field, Of course I could load that field twice and have say



DeliveryDate as CalendarDate


and then I would link Order table to Calendar Table.

If I did this with Delivery table as well then I'd get synthetic keys I'd expect. Is this an issue or not? Having done this I'm still not sure which field I should actually use for a weekly, monthly, quarterly aggregation pivot table.

I thought of joining tables, and/or creating a separate additional table with just the date fields from the different tables but the avenues I've tried are getting me into knots.

I'm sure my example is a common type of scenario. Maybe I'm searching with inadequate keywords but I havent yet found anything to point me the the right direction in the community forums.

What are good ways to tackle such a sort of drilldown / aggregation effectively?


1 Reply
Not applicable

Re: Drilldown / aggregation by multiple date fields

Hmmm, of course as usually happens I find something potentially promising *after* posting.

I'm going to try this - method 2 and see how I go.