Forum Discussion
Help with Hourly Data Model
I have tried over several days to figure this out without success. Throwing it out here with my sample data file.
I have a table with hourly data. However, I have also created an aggregated summary table on a day level ( for easier connection to my Calendar table) . Then I have a calendar table. Most of my analysis / reports is done on the summary table. However on occasion, I need to analyze the hourly data. I am finding it difficult to relate the two tables - the original data table and the summary. My Issues:
- I cant create an index on the summary calculate table to connect the two tables
- I cant use slicers built with data from the summary table to drill into hourly data from the data table
Sample File attached ......Sample File
11 Replies
Does your hourly data have a date stamp as well as time? If so, you can extract just the date and use that field for your relationship.
With that said, I'm not sure I understand what you are trying to do? You want to be able to see hourly granularity when you filter by date?
I tried your suggestion about extracting just the date to use as a relationship field - didn't work . The date field is not unique in both data and summary table.
I am am looking to have access to hourly granularity for charts or for example average value by hour aggregated across several days
In the query editor, make a reference table that points at your hour stamp column.
Extract date.
Remove the time column.
Remove dups of date column.
Use this table to connect both your date field of your data table and the aggregated table.
In other words, this referenced table will sit between the two tables you wish to create a relationship between.
Hi juju,
I'm talking about the issue you write in the same files. You need to place the Channel column from the DataTable in the slicer to filter the data. See:
Best Regards,
Qiuyun Yu
