Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Multiple dates in tables+Master Calendar--which one is my MC date?

Attached is a sample data coming from my ERP data source. I am having trouble identifying which date is my true master calendar date. Each date is needed as they are based on final calculations, so no dates can be removed/deleted.

A total of 5 tables with dates squared in red:

I have tried the join function, but with errors.

I have also added a 'Goal' sheet tab in the attached Excel for my end goal.

As always, thank you for the help!

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

If you fix a minor bug, I think it would work fine. The bug is that you're joining the Asset_Master before all of the transactional tables are added. Leases only have dates of the first of each month. And so you only have a description and status associated with the first of each month. So if you select a status, say, you will only get records that have a date of the first of a month. I believe it would be easily fixed by just waiting until all the transactional tables are added so that it joins to every row. Alternatively, just leave the Asset_Master as a separate table.

When you're joining rows rather than concatenating, you lose the ability to have a generic Type and Quantity, which can be useful in charts. But if you don't feel the need for those, you can do the joins like you have them, and add up the totals as you've done.

So, easily fixed bug, not how I'd have done it, but close, and should work fine. Thumbs up.

View solution in original post

5 Replies
johnw
Champion III
Champion III

I would redo the data model like this:

Asset Master
Asset_ID
Description
Status

Transactions
Asset_ID
Date
Type
Quantity
Lease_Cost
Fuel_Amount
Depreciation_Amount
Maintenance_Cost

Calendar
Date
Month

So you leave your Asset Master table alone, and then you add Transactions table that is a concatenation of all the other data. For the leases, create an entry for every month a given lease is applicable (while loop, maybe). For Fuel, Depreciation, and Maintenance, just concatenate them in with the dates they have already.

For your chart, you have a couple options. Probably simplest is to use the Type and Quantity fields. Our types would be Leases, Depreciation, Fuel, and Maintenance. Our Quantity would be whatever quantity is associated with those. So just add Type as a column, and do a subtotal on it. But then we also have the specific quantity fields available so that we don't need to do set analysis to get something specific. So you could instead build your chart with multiple expressions instead of a Type dimension, and do a sum(Lease_Cost), sum(Fuel_Amount) and so on, and the total is just another column that adds them all up.

Not applicable
Author

Is the expectation to rename every date field as 'Date' to tie with the master calendar?

johnw
Champion III
Champion III

The expectation is that you replace four of your tables (Leases, Fuel, Maintenance, and Depreciation) with a single Transactions table, and build the rows and set the Date field in the Transactions table as noted. I suppose you could think of it as renaming all your date fields to "Date", but it goes well beyond that. You won't get very far if you merely change them all to "Date" without changing the overall structure, and the Leases table wouldn't even load because it would have two fields named "Date".

Not applicable
Author

John-see attached. What do you think of this model?

johnw
Champion III
Champion III

If you fix a minor bug, I think it would work fine. The bug is that you're joining the Asset_Master before all of the transactional tables are added. Leases only have dates of the first of each month. And so you only have a description and status associated with the first of each month. So if you select a status, say, you will only get records that have a date of the first of a month. I believe it would be easily fixed by just waiting until all the transactional tables are added so that it joins to every row. Alternatively, just leave the Asset_Master as a separate table.

When you're joining rows rather than concatenating, you lose the ability to have a generic Type and Quantity, which can be useful in charts. But if you don't feel the need for those, you can do the joins like you have them, and add up the totals as you've done.

So, easily fixed bug, not how I'd have done it, but close, and should work fine. Thumbs up.