Thanks for your response, Deepak!
Sorry if i am not clear. Please ignore the data included in the workbook. Trying to create a calculated field which when displayed as user filter should show last 6 month names as values in the filters. Able to show just the current month i.e. October in the filter selection. Want to show September, August, July, June, May months as well in the filter
Here it is:
My data set has data upto December so Giving you from July to December Filter. You can use your data upto October and Replicate
If it Helps, Pl Mark it Helpful and CORRECT to close Thread
Thanks for responding, Deepak!
I am looking to get last 6 month (in MMM-YYYY format) names using a calculated field and display it as filter based on today's date. Any suggestions?
I'm not sure my solution is that much different from Deepak's
First I created a filter, so only include the last 6 months (based on the current month we are in)
[Order Date Filter]
DATETRUNC('month',[Order Date]) <= DATETRUNC('month',today())
DATETRUNC('month',[Order Date]) >= DATEADD('month',-5,DATETRUNC('month',today()))
I then bring this onto the filter shelf and set to true.
I then created a field, which is just the month trunc of the date field
[Order Date Month]
and set the formatting to
Exposed this field as a filter, and set to Only Relevant Values
Hope that helps, but let me know if that isn't what you need (and where it fails)
Last 6 Months Filter.twbx 1.1 MB
Sorry for the delayed reply. I want to use Today() as basis to calculate last 6 months and show it as filter to control views in dashboard. Shouldnt be using a date field to create a filter
So if there is no date field in the data (in which to create the last 6 month filter) how will it know what to filter on the dashboard?
Say we have this data
Product Sales A 100 B 200 C 300
And we tell the filter to filter this down to the last month...how would it do it?
For the purpose of creating filter, we shouldn’t use any date field from the extract. However once we have the filter created, we can compare the date selected in the filter with the date field to display the view..
please let let me know if it clarifies
It does, sort of! but filters are created from fields, so I don't know a way to create a filter from no data (or not from a field). If you apply the logic that either myself or Deepak has used to the date field (even if the data isn't fully there yet) once you switch over to some real data the logic will persist and show the last 6 months.
I guess you could create a dummy data source which created a row for each of the last 6 months, and create a filter from that. You'd then "blend" your real data against this to create a filter, but there would be an un-necessary performance hit (from using a blend, when you no longer need it once you have a real date field).
You could also use a parameter, which you could set up without any data, but as it's not dynamic (in that the values in the parameter won't change when today() is next month), you'd have to use a number so 1,2,3,4,5,6 indicating if you wanted to look back 1 month, 2 months...etc.
If you can let me know a bit more about why you want to take this route I can have a think on another way (it's not something I've ever encountered, so can't think when someone would want to do this)
Ah so you have 2 date fields!
So when someone selects, say, August 2018 they would see all entries where the Date Updated OR the Date Deleted was in August 2018? is that right?
Also do you have any ability to re-shape the data? One easy way to do this would be to pivot the data so that you had a single date field, where there would be a dimension of Added or Deleted?
If you don't I'll have to have a bit of a think!
Not sure if pivoting helps as i need separate views using each of the date fields
Attaching the latest workbook i have. In dynamic filter worksheet, dynamic date filter is created with last 6 month names. Since i want to use the value selected by user from dynamic filter to be matched with
- 'date updated' field to show Updated view and
'date deleted' field to show Deleted view
Used action filter to do the matching (not sure if there is another approach to control 2 views (i.e. date fields) using value selected in a single date filter). This approach looks clumsy. 'Go' button doesnt look pretty too