Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
prees959
Contributor II

Link Month Level Data

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

2 Replies
Digvijay_Singh
Honored Contributor III

Re: Link Month Level Data

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.

MVP
MVP

Re: Link Month Level Data

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]

  ...

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein