https://community.qlik.com/t5/Suggest-an-Idea/Compose-Aggregate-Fact-Dynamic-Transaction-Date/idi-p/1975318 <P>Current state (Compose 2022.5):</P> <P>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 <SPAN>are available as “business supplied” attributes (i.e., hire date, birth date, order date, first sales date, etc.).&nbsp; 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.&nbsp; 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), </SPAN>this value would have to be materialized in a table <SPAN>before entering the wizard.&nbsp;&nbsp;</SPAN></P> <P>&nbsp;</P> <P>Desired state:</P> <P>Enable the capability for dynamic dates to be input for the transaction <SPAN>date in the absence of a traditional “transaction date” (which may not always be supplied by the source system(s) consistently.&nbsp; </SPAN>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.</P> <P>&nbsp;</P> <P>Business need:&nbsp;</P> <P>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).&nbsp;</P> Wed, 31 Aug 2022 12:03:18 GMT srstage80 2022-08-31T12:03:18Z https://community.qlik.com/t5/Suggest-an-Idea/Compose-Aggregate-Fact-Dynamic-Transaction-Date/idc-p/2054350#M12379 <P>Hi, thanks for the suggestion.&nbsp; &nbsp;</P> <P>Aggregate facts use the 'current data' with the transaction date only used to connect the 'current data' with your type 2 dimensions.&nbsp; &nbsp;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)&nbsp; is also based on a specific point in time ?</P> <P>&nbsp;</P> <P>Setting this to collecting feedback from the community and&nbsp;<a href="https://community.qlik.com/t5/user/viewprofilepage/user-id/169555">@srstage80</a>&nbsp;</P> Tue, 28 Mar 2023 19:18:08 GMT https://community.qlik.com/t5/Suggest-an-Idea/Compose-Aggregate-Fact-Dynamic-Transaction-Date/idc-p/2054350#M12379 TimGarrod 2023-03-28T19:18:08Z https://community.qlik.com/t5/Suggest-an-Idea/Compose-Aggregate-Fact-Dynamic-Transaction-Date/idc-p/2100067#M13335 <P>From now on, please track this idea from the Ideation portal.&nbsp;</P><P><STRONG><A title="Link to new idea" href="https://ideation.qlik.com/app/#/case/273644" target="_blank" rel="noopener">Link to new idea</A></STRONG></P><P>Meghann</P><P data-unlink="true"><EM>NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you <STRONG>only</STRONG> see 1 tab with the login page, please try clicking this link first: <STRONG><A title="Authenticate me!" href="#" target="_blank" rel="noopener">Authenticate me!</A></STRONG>&nbsp;t</EM><EM>hen try the link above again. Ensure pop-up blocker is off.</EM></P> Wed, 02 Aug 2023 15:13:35 GMT https://community.qlik.com/t5/Suggest-an-Idea/Compose-Aggregate-Fact-Dynamic-Transaction-Date/idc-p/2100067#M13335 Meghann_MacDonald 2023-08-02T15:13:35Z https://community.qlik.com/t5/Suggest-an-Idea/Compose-Aggregate-Fact-Dynamic-Transaction-Date/idc-p/2100068#M13336 Wed, 02 Aug 2023 15:13:36 GMT https://community.qlik.com/t5/Suggest-an-Idea/Compose-Aggregate-Fact-Dynamic-Transaction-Date/idc-p/2100068#M13336 Ideation 2023-08-02T15:13:36Z