Analytical Need
Often times, we are required to present all the existing records in our dimension whether we have or don't have records to aggregate for it from our fact table or simply, perform a left join on-demand instead of the by-product inner-join.
The Challenge
In our example, we need to be able to see all the dates from the date dimension and the total sales per day for the account that we select.
Tables:
Account
Sales
Dates
Ideally, for example, the user should be able to filter account: A and see the following result:
Solution
To solve this issue, we will need to download the Filtered Measure plugin (certified) no modeling solution is required.
The idea will be to use the Filtered Measure to create an OR statement on the formula.
This will allow us to enforce the 'Account' filter only on the formula level and not affect the retrieval of all the possible dates.
Steps:
Add an Account filter to the dashboard.
- Create a widget and add 'Days in Date' to the Rows and the following formula for the aggregation:
- (sum([Total Sum]),[@Account] )
Aggregate the SUM from the FACT and pick the Account as [All Items] as a filter in the second part of the brackets: - Rename the Account to '@Account'. This will let the plugin know to enforce the filter only on this level.
- Add a calculation from the Dates dimension as Unique count of days * 0
The filter will not be enforced on this calculation, hence, showing all the dates. - Test.
Result:
Account A:
Account B:
Cube & Dashboard reference: LeftJoin.ecdata, LJTEST.dash
That's it!
