Philosophical question re dates

[josephinetedesc]

Hi I have 2 tables 

table A (dates from April to August) - Sales achieved

table B (dates from Jan to Dec) - Target Sales

And I created a Master Calendar ( so both table A and table B) have a line in the script linking them to the Master Calendar.- called commonDate.

Then I found

Selecting Arbitrary Date Ranges

This was simple to follow (thank you qlkview community) - this is good because it allows a user a date using the calendar object.

My problem is:

I have list boxes called Month, Year, WeekNo - which I link to Calendar date (commonDate)?

However

the Month List box has months from Jan to Dec although there is no Sales Achieved data from August to December.  So not incorrect but annoying to users.

The other problem is that my chart with Months as a dimension on the x axis, get out of kilter when someone chooses Month from the List box.

I think I will need to send up an attachement to show what I mean.

Jo

[petter]

I think this information about Canonical Date  from Henric Cronström‌ could be very interesting and helpful reading for you too.

What you are dealing with is different dates that play different roles. In data warehouse terminology it is often referred to as role-playing dimensions or alias dimensions. Dates are often the most common role-playing dimension.

In your case the date have to roles both as a Sales "role" and also as a Target "role". They can be dealt with in different ways but HIC's article about Canoncial Date is very clear and informative on the matter when it comes to a QlikView approach.

[hic]

In this specific case, I would probably load the sales numbers and the target numbers in the same (concatenated) fact table, and still just use just one master calendar. If both sales and target numbers are by month, this is straightforward. But if you have different granularity for the two, you need to load it using generic keys. See Fact Table with Mixed Granularity.

If you think it is annoying/incorrect to show months where there is a target, but no sales, you could consider filtering away these records already when you load the data.

HIC

[josephinetedesc]

Thank you both.  I will need to be at work to get the qvd files to work.

What I have done is use the daily report date  as daily date and then also load the daily report date as the commonDate.  I have also loaded the daily target date as targetDate  and also loaded the date as the commonDate.  So the daily report date - is today + 31 days, but the target date is available till December. 

There should be no need to choose a particular month or week  as the x axis of the charts shows monthly and week results.

However when users try to show the data at a weekly or daily level, this is a problem with the results.  I suppose it would be better to have a separate sheet for the executive views and then when they want to delve they can use a different sheet?  Or maybe I could have another chart which shows the max month and then only for the max week?  So many possiblities!

Jo