How to use a parameter to change a visualization's axis granularity and range

[ameliahelland]

Sometimes you might want to change not only the granularity of a visualization’s X-axis, but the range as well. This is possible using parameters and Switch statements.

1. Create a list control parameter called [Date-Granularity] with the following settings:

   
   →
   

2. Add a table (with a date column) to your Workbook on a hidden page, then:

   • Group by [Date] to get [Day of Date] (Sigma will auto-truncate each date to day of date).

3. Create a child visualization from the parent table and use the following formulas in the X/Y axes:

   • X-axis:
     Switch([Date-Granularity], "Daily (Last 90 Days)", [Day of Date], "Weekly (Last 6 Months)", DateTrunc("week_starting_sunday", [Day of Date]), "Monthly (Last 12 Months)", DateTrunc("month", [Day of Date]))

     • The above will check if the [Date-Granularity] parameter is set to “Daily (Last 90 Days)”, “Weekly (Last 6 Months)”, or “Monthly (Last 12 Months)”. Then, it sets the X-axis values to be the dates truncated to the corresponding level (i.e., Day, Week, or Month).

   • Y-axis:
     Switch([Date-Granularity], "Monthly (Last 12 Months)", SumIf([Cost], [Day of Date] >= DateTrunc("month", DateAdd("month", -12, Today()))), "Weekly (Last 6 Months)", SumIf([Cost], [Day of Date] >= DateTrunc("week_starting_sunday", DateAdd("month", -6, Today()))), SumIf([Cost], [Day of Date] >= DateAdd("day", -90, Today())))

     • The above changes the Y-axis formula depending on which [Date-Granularity] setting is selected. Note that each SumIf statement returns the sum of the [Cost] column wherever [Day of Date] is after or equal to the minimum date that is X days/months before today.

4. Filter out the Nulls in the Y-axis column:

   • Right-click the Y-axis column and select “Filter”
     

   • Click the 3-dot kebab menu icon and de-select “Include nulls”
     

Voila!

This changes the minimum date and date granularity shown on the visualization.

[zwhitcomb]

@ameliahelland - this is very cool, and something I had wondered about.

Is it still possible for the user to arbitrarily select a shorter or longer date range once the parameter value is selected? (e.g., they want a monthly view for the last 18 months, or the last 6 months?) Or would you need to set the parameter for the longest range for that date unit, then have a separate filter/control to limit the range?

[ameliahelland]

If I understand correctly, I don’t think it would work to arbitrarily select a longer date range once the parameter value is selected (because the Y-axis formula is hard-coding in the minimum dates). That said, it should be possible to select a shorter range after the fact (by filtering on the X-axis Date column).

You could potentially set up the solution differently though, so that you choose “Daily”, “Weekly”, “Monthly” in one parameter and then input the time range in days, e.g. 180, into another parameter (but then you’d need to update the Y-axis formula to reference both parameters).

[data_katrina]

100% I find it often comes down to the preference of the user if they want the control to choose a variety of date combinations or a set list. If I’ve got conflicting user requests I’ll give all the flexibility but then use buttons and parameters to create “quick starts” so someone can easily navigate to some common scenarios.

[AdamD]

We have been using this approach from the beginning on just about every dashboard we create… Daily, Weekly, Monthly, Quarterly, Yearly. Really helpful functionality for our end-users!