Skip to main content

Suggest an Idea

Vote for your favorite Qlik product ideas and add your own suggestions.

Qlik Connect 2024! Seize endless possibilities! LEARN MORE

Compose | Aggregate Fact Dynamic Transaction Date

Contributor III
Contributor III

Compose | Aggregate Fact Dynamic Transaction Date

Current state (Compose 2022.5):

When creating an aggregate fact, Compose only permits the selection of a transaction date field that is sourced from the tables involved in the creation of the fact and are available as “business supplied” attributes (i.e., hire date, birth date, order date, first sales date, etc.).  When a date field is selected from a source table, that date is used to query all the other related tables using the internal FROM_DATE and TO_DATE satellite columns to return the data set.  In order to establish an aggregate fact that is based on CURRENT_DATE or any other point in time (i.e., last day of the year or the FROM_DATE from the transaction), this value would have to be materialized in a table before entering the wizard.  


Desired state:

Enable the capability for dynamic dates to be input for the transaction date in the absence of a traditional “transaction date” (which may not always be supplied by the source system(s) consistently.  Examples such as CURRENT_DATE or CURRENT_DATE -1, CURRENT_DATE -30, etc, alleviating the need to work around the limitation of the transaction date selection.


Business need: 

Aggregate fact views that require use cases such as the current state of all transactions (CURRENT_DATE) or the state of all transactions 30 days ago (CURRENT_DATE -30) or yesterday (CURRENT_DATE -1). 

Tags (1)

Hi, thanks for the suggestion.   

Aggregate facts use the 'current data' with the transaction date only used to connect the 'current data' with your type 2 dimensions.   Is the request to adjust how the dimension surrogate keys are defined, as this sounds more like supporting a point-in-time based aggregate where the fact data (eg the aggregated KPI)  is also based on a specific point in time ?


Setting this to collecting feedback from the community and @srstage80 

Status changed to: Open - Collecting Feedback

From now on, please track this idea from the Ideation portal. 

Link to new idea


NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you only see 1 tab with the login page, please try clicking this link first: Authenticate me! then try the link above again. Ensure pop-up blocker is off.

Explorer II
Explorer II
Status changed to: Closed - Archived