Skip to main content
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
Showing results for 
Search instead for 
Did you mean: 

Showing trended metrics based on different date fields


I have a table “Trended Data” that contains monthly data and is unique on Parts ID & Data As Of Date where Data As Of Date is my month end dates. I have another table “Completed Dates” that contains three columns Part ID, Completed Date, Cost. Completed Date does not have to be a month end date.  I would like to have the ability to show two different things on a sheet using these two tables.

  • Show a trended bar chart with “Data As Of Date” as my dimension with various metrics from the “Trended Data” table. Along with those metrics I would like to show a measure for the field “Cost” from the “Completed Dates”. If I create a measure “SUM(Cost)”, then I would receive a constant value of total Cost for all parts across each Data As Of Dates. I only want to sum up the Cost of all parts when the month end date of Completed Date is equal to Data As Of Date. (Remember Completed Date can be a mid-month date.) The equation listed below only works if I looked at parts with the same Completed Date.

     Sum(if([Data As Of Date]='$(=Date(MonthEnd(floor([Completed Date])),'MM/DD/YYYY'))', ([Cost]),null()))

  • On the same trended bar chart, I I would also like to see metrics from the “Trended Data” table for the month-end prior to the “Completed Date” for a Part ID in a bar chart with “Data As Of Date” as the dimension. For some metrics, I will only want to see this value in the month of completion while other metrics I will want to see it in the month of completion and forward.  For example, from the Trended Data table I have fields “Estimated Return” and “Projected Cost”. I would like to see the “Estimated Return” value from the month before completion for all Data As Of Dates during and after the Completion Date. For “Projected Cost”, I would only like to see the value from the month before completion for just the Data As Of Date equal to the month end of Completion Date.  I have shown an example below for some sample data.

I have listed an example of data for just two Part IDs (imagine I have a lot more than just 2).

Trended Data Table


Part IDData As Of DateProjected CostEstimated Returns

Completed Dates Table

Part IDCompleted DateCost

Trended Data.png

I’m open to updating the loading script but I’d like to be able to develop some logic with a formula on a sheet because it would allow all users to be able to add more metrics on their own.

Thank you,


0 Replies