Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Friends,
I'm making a report for an assurance company. This report have just one fact table with differents dates related to a Principal Key that represent a unique code for the registered accident. The differents metrics have to be represented on a timeline chart using this dates fields. Here is an example of the fact table.
PrincipalCode | Date1 | Date2 | Date3 | Date4 |
---|---|---|---|---|
A | 01/01/2015 | 01/01/2016 | 01/02/2016 | 20/02/2016 |
B | 01/10/2015 | 01/12/2015 | 01/01/2016 | 20/01/2016 |
C | 01/03/2014 | 01/06/2015 | 01/07/2015 | 30/07/2015 |
D | 01/04/2012 | 01/10/2015 | 31/10/2015 | 30/11/2015 |
I want to relate this dates to a principal calendar, I can't use Link Tables because I have only one fact table, I'm trying to solve this using differents master calendar, its a good idea?
Thanks for your help
Best Regards
Try to use a CROSSTABLE LOAD prefix
This will create a single column containing all your dates and another column with the labels ('Date1','Date2'..)
You can link the single date column easily to a master calendar
First do the transformation using Cross Load, like this?
And then see this to create a Master Calender?
By using the rangesum to solve the problem or by using the cross table
Hi Swuehl,
Thanks for your answer,I use crosstable and "yes" it works fine if I want to select differents kind of dates. But what happend if I need to show the information like this on a chart.
Date2 | 01/01/2015 | 01/02/2015 | 01/03/2015 | 01/04/2015 | 01/05/2015 | 01/06/2015 | 01/07/2015 | 01/08/2015 |
Date1 | ||||||||
01/01/2015 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 |
01/02/2015 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | |
01/03/2015 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | ||
01/04/2015 | 1000 | 1000 | 1000 | 1000 | 1000 | |||
01/05/2015 | 1000 | 1000 | 1000 | 1000 | ||||
01/06/2015 | 1000 | 1000 | 1000 | |||||
01/07/2015 | 1000 | 1000 | ||||||
01/08/2015 | 1000 | 1000 |
Have you tried a pivot table chart and two dimensions, like
=If(DateType = 'Date1', DateField)
=If(DateType = 'Date2', DateField)
with DateType and DateField being the two fields created by the CROSSTABLE LOAD.