Drilldown / aggregation by multiple date fields

Tue, 07 Aug 2012 15:28:00 GMT

Hi,

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.

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.

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

LOAD

DeliveryDate,

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?

Thanks

Re: Drilldown / aggregation by multiple date fields

Tue, 07 Aug 2012 16:18:00 GMT

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.

http://community.qlik.com/message/30971#30971

topic Drilldown / aggregation by multiple date fields in QlikView

Drilldown / aggregation by multiple date fields

Re: Drilldown / aggregation by multiple date fields