Link Month Level Data

[prees959]

Hi,

I have a main dataset that has a Date-Day as its Date Dimension.  I need to link a table that has Month as it's lowest Date Dimension

Fields in Main Dataset :

Date as (dd/mm/yyyy) 

Driver

Area

Total Miles

Fields in Second Dataset:

Date as MM-YYYY  (eg Jun-2017)

Driver

Area

Total Earnings

Can anyone advise on how to link the Month level data to the main dataset without it duplicating?  I need to be able to show the monthly target for each driver, so when the user selects a date - another summary tab can show the Earnings at month level.

Many thanks,

Phil

[Digvijay_Singh]

You may think of creating master calendar with date, month, year etc fields. Join the month field of master calendar to bottom table and date field to top table. I think Qlik will provide you all the required information through dynamic association created on the fly when chart is visualized and dimensions and measures calculated on the fly.

[jonathandienst]

Like this:

Table1:

LOAD Date,

  Date(MonthStart(Date), 'MM-YYYY') as DateKey,

  Driver,

  Area,

  [Total Miles]

  ...

Join (Table1)

LOAD Date(MonthStart(Date#(Date, 'MM-YYYY')), 'MM-YYYY') as DateKey,

  Driver,

  Area,

  [Total Earnings]

  ...