could you paste sample output you are expecting based on data provided...? thanks
As an additional note, here is the SQL I used to calculate the "state" for a given date column when manually creating the example chart I am trying to achieve:
select HT1.RELATION_ID, HT1.IMPORTANCE, HT1.STATUS, HT1.MODIFIED from (
select * from (
select * from HISTORY_TABLE HT3 where HT3.MODIFIED <= '2017-12-15' order by HT3.MODIFIED desc
) as HT2 group by HT2.RELATION_ID
) as HT1 where HT1.STATUS != 'Closed'
So, starting from the most inner select, first I want all records earlier than "THIS DATE" where "THIS DATE" is the value from DATETRUNC('week', MODIFIED) used as my column in the view. Then, I find the most recent record using order by and then a group by in the second select. And finally, I remove closed records from the final select to get those records that were OPEN at that time.
I've tried wrapping my head around the FIXED, INCLUDE, and EXCLUDE LOD keywords to try and get the filtering precedence right, but I can't quite figure out how those map to the above SQL.
Any help would be much appreciated!!
Oh, and use the attached updated History-Mountain-v2.twbx workbook because in my haste to cleanse my data of anything sensitive I messed up the open/closed counts in the original version.
History-Mountain-v2.twbx 117.2 KB