Filtering on 2 dimensions and counts
Hi community,
I am struggling to do something that I think should be possible so asking for help here 
I have a star schema modelled in snowflake and a Direct Query model in Pyramid consisting of a fact table and 2 dimension tables.
The fact table is a factless fact table used for counts and contains a single record for each policy where the policy_count field contains the value 1. The 2 dimension records relate to the Start Date of the Policy and the End Date of the policy.
The business requirement is to produce a report that returns the number of live policies at specific date, where the user is prompted to select a single date. A policy is considered live if the selected date is greater that or equal to the Policy Start Date and less than or equal to the Policy End Date.
For example, if I had 3 policies in the system that overlapped as follows:
The user should be prompted to enter single date and return 1 if they entered a date corresponding to Date A, 3 if they entered a date corresponding to Date B, and 2 if they entered a date corresponding to Date C.
Obviously I have simplified the example here but there are many other dimensions available and the solution needs to take into account any other dimension that has been selected so that "group bys" work.
Many thanks for you help.
Malcolm
2 replies
-
try this out, maybe it does what you're after.
- create a new date table: either a new real table; a new database view; or as a virtual table in Pyramid's table editor view using custom SQL. Ensure it captures the full range of relevant dates that can occur between start and end.
- Put a join from the new date table to the start date table, and use a different join operator other than "=" between the data fields. ">=" or "<=" should work.
- Repeat the join again from the new date table to the end date table. Change the join operator to the other direction.
- Expose the new data table as the attribute to pick from - rather than start and end.
PS: it may be better and more correct to skip the start and end date tables and go straight to fact table with a double key join.