How would you like to specify the breaks? By years, quarters, months, etc... or by something more specific?
preferably I would like to specify to a specific time range. As an example, from 01.01.2010 to 01.01.2014 in one continous line, and then the remaining after that in one.
1 of 1 people found this helpful
So, something like the example below, correct?
Ok, it seems to do what you were looking for, so I'm going to explain it, as it has helped me on a current project
You will have to create a 'From' and a 'To' parameter using your 'Date' dimension. Change the 'Display Format' of each parameter to mm/yyyy.
First calculation you will create is 'Date Range' (this will determine whether the date range of the parameters is True or False):
if ([Date From]>=[Order Date]
[Date To]<=[Order Date])
then 1 else 0 end = 0
Next, you will need to separate the date into 3 parts:
if ([Order Date]>[Date From] and [Order Date]<[Date To]) then '2'
elseif ([Order Date]<[Date From]) then '1'
elseif ([Order Date]>[Date To]) then '3'
Finally, you can choose to name the date ranges:
case [Range Filter]
when '2' then if ([Order Date]>[Date From] and [Order Date]<[Date To]) then str([Date From])+ ' to '+str([Date To]) end
when '1' then if ([Order Date]<[Date From]) then 'Prior to ' +str([Date From]) end
when '3' then if ([Order Date]>[Date To]) then 'After ' +str([Date To]) end
Now, placing your pills:
Column Shelf: 'Date' (change to discrete and make mm/yyyy), 'Date Range Filter' and 'Range Filter' (organize your vales of the Range filter from 1 to 3, from left to right, by dragging the header)
Row Shelf: your measure (Sales, etc.)
Color Shelf: 'Date Range' (this will highlight the range you have selected)
Hope this helps.
that is exactly what I am looking for.
That's great to hear! I'm glad that I was able to help.
Thank you very much!
will have see if I can get this to work tomorrow.