Do you have a client that wants to change the axis of the graphs in your dashboard to flip from Days, Months, Weeks, Quarters or Years? You can do this with a parameter and a calculated field. I created a tech tip on how to do this before I had my blog site!
Review the below steps for details;
In this example I connected to the Sample Superstore dataset.
Steps:
Place Order Date on your Columns shelf formatted as Day(Discrete)
Put Sales on your Rows shelf as a SUM aggregation.
Place Region on your Color marks card.
You should have the below visualization
Create a string parameter called Date Granularity in the format as shown below;
Right-click on this parameter and select Show Parameter. (Don't change anything yet and ensure it is on Day as the current value.)
Right-click on this parameter and select Create > Calculated field.
Rename this field Date Filter using the following calculation;
Case [Date Granularity]
when 'Day' then DATETRUNC('day',[Order Date])
when 'Week' then DATETRUNC('week',[Order Date])
when 'Month' then DATETRUNC('month',[Order Date])
when 'Quarter' then DATETRUNC('quarter',[Order Date])
when 'Year' then DATETRUNC('year',[Order Date])
else [Order Date]
END
Change the Data Type of this calculated field to be a whole number. (Right-click Change Data Type > Number (whole).)
Replace the Order Date on the Columns shelf with the Date Filter calculated field and ensure it is formatted as Discrete and a Dimension.
Then go back to the Data Filter calculated field in the Data pane and format it as a Date.
Right-click on the Axis and select Format to format it as a Custom Date by entering DD MMM YY in the format pane as shown below;
Now you can filter the graph using the Date Granularity parameter. It will filter the line graph.
Comments