topic Link Month Level Data in QlikView

Link Month Level Data

prees959 — Mon, 12 Mar 2018 23:19:10 GMT

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

Re: Link Month Level Data

Digvijay_Singh — Tue, 13 Mar 2018 01:26:19 GMT

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.

Re: Link Month Level Data

jonathandienst — Tue, 13 Mar 2018 05:05:45 GMT

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]

...