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

Showing trended metrics based on different date fields

Hi,

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
AAA3/31/20184565
AAA4/30/20185070
AAA5/31/20185570
AAA6/30/201800
AAA7/31/201800
AAA8/31/201800
AAA9/30/201800
BBB3/31/20188097
BBB4/30/201890120
BBB5/31/2018100120
BBB6/30/2018110120
BBB7/31/201800
BBB8/31/201800
BBB9/30/201800

Completed Dates Table

Part IDCompleted DateCost
AAA6/28/201860
BBB7/10/2018105

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,

Brian

0 Replies