-
1. Re: Filter by Parameter - Date Range
Jim DehnerFeb 5, 2018 7:26 AM (in response to Stephen Morgan)
Hi Stephen
several ways t do this - below is a formula that if a t/f filter
DATETRUNC('year',([Parameter 3]) )= DATETRUNC('year',[Order Date])
and DATETRUNC('month',[Parameter 3])>= DATETRUNC('month',[Order Date])
OR
DATETRUNC('year', dateadd('year',-1,([Parameter 3]) ))= DATETRUNC('year',[Order Date])
and MONTH([Parameter 3])>= MONTH([Order Date])
it will return this
I included the months just for the example you can exclude them and the filter will return the total
Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
-
2. Re: Filter by Parameter - Date Range
Stephen Morgan Feb 5, 2018 7:57 AM (in response to Jim Dehner)Hi Jim
Many thanks for this. Almost there but it only returns the corresponding period from the prior year e.g. (I'm working on a quarterly basis) for Q1 2018 it only returns Q1 2017 where I need Q1, Q2, Q3 and Q4 for 2017 plus Q1 for 2018.
-
3. Re: Filter by Parameter - Date Range
Stephen Morgan Feb 5, 2018 8:16 AM (in response to Stephen Morgan)I've removed the last month clause and looks ok. Thanks again.
-
4. Re: Filter by Parameter - Date Range
Jim DehnerFeb 5, 2018 8:06 AM (in response to Stephen Morgan)
if this meets the need pleas mark my reply correct to close the thread
if you are looking at Quarters specifically the datetrunc formula will recognize datetrunc('quarter',your parameter') which will be easier to use than month
the way datetrunct works is it will look at the first day of the quarter i.e. Jan 1, April,1 Jul1, Oct1 and those can be used in the comparison
DATETRUNC('year',([Parameter 3]) )= DATETRUNC('year',[Order Date])
and DATETRUNC('quarter',[Parameter 3])>= DATETRUNC('quarter',[Order Date])
will look at the current year through the entire current quarter
Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.