1 Reply Latest reply: Aug 7, 2012 12:18 PM by M Paeper RSS

    Drilldown / aggregation by multiple date fields

    M Paeper

      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.

      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

       

      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