cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

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

Community_Admin_0-1634215319501.png

Sales

Community_Admin_1-1634215319353.png

Dates

Community_Admin_2-1634215319374.png

Ideally, for example, the user should be able to filter account: A and see the following result:

Community_Admin_3-1634215319371.png

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:
Community_Admin_4-1634215319365.png

 

  • (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:
    Community_Admin_5-1634215319359.png

     

  • 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:

Community_Admin_6-1634215319396.png

Account B:

Community_Admin_7-1634215319351.png

Cube & Dashboard reference: LeftJoin.ecdata, LJTEST.dash

That's it!



Version history
Last update:
‎10-14-2021 05:42 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request