InfoSol We're passionate about inspired business intelligence
Have you ever had the need to display a full year of data on a chart … even when you don’t have actual data to display?
| This is what we want to display: | But this is what our data displays: |
|
|
 |
We used to use complicated work arounds to handle this requirement, involving bringing in another query, merging dimensions, etc. However, with the new Time Dimension feature added in Webi 4.2 sp6, now we can easily fill in the missing months.
Starting with a Date dimension, right click on the object to view the “Create Time Dimension” feature.
Select the Quarter & Month hierarchies and click OK. (Quarter is not necessary for this scenario, but I added it anyway. The same principle would work for Quarter, Week, or days as well).
Then we can create a table with Month of Order Date and Number of Orders, which will look like this:
Since we want to turn this into a chart, I want to shorten the month name, so I will apply a left function to the Month of Order date object:
=Left([Order Date Time Dimension].[Month of Order Date];3)
Save this as Short Month. Notice though, that now we have lost the inherent sort that is a bonus of the Time Dimension functionality. We could correct this a couple of different ways. We could create a custom sort order for Short Month. Or, we could add the Month of Order Date back in as the first column, then hide it:
Right click on the table and turn it into a column chart.
Alas, it looks like we lost our sort! But no worries – just right click on the outer edge of the chart and choose Sort >Month of Order Date > Ascending.
This will give us a proper sort once more:
However, this is where we want to display the entire year rather than only the months with actual data. To extend out the months for the rest of the year, right click to Edit the Time Dimension:
Under the Calendar options at the bottom, check the box for “Set range dates” and fill in the beginning and ending year dates. (This works with Fiscal Calendars as well.) Click ok.
You will see that we now have an entire year displaying in the chart, and as new orders are added to the data, they will display in the chart.
Woohoo – easy full year chart!
This also works for filling in gaps of missing data as well:
Try the Time Dimensions out in your environment and see if this is a good fit for your data display.
