6 Replies Latest reply on Oct 22, 2019 12:13 AM by Tom Fowler

# Dynamic X-axis based on the value in Parameter

Hi All,

I like the x-axis to show Month, Year, Quarter based on the parameter period value in the attached workbook.

Thanks

Jag

• ###### 1. Re: Dynamic X-axis based on the value in Parameter

hi Jagjit,

So I added this calculation to format the date to the required level

[Select Period Label - SR]

CASE [Period]

WHEN 'Year' THEN STR(YEAR([Select Period]))

WHEN 'Month' THEN LEFT(STR(DATENAME('month',[Select Period])),3) + '-' + RIGHT(STR(YEAR([Select Period])),2)

WHEN 'Quarter' THEN STR(YEAR([Select Period]))+" Q"+ DATENAME('quarter',[Select Period])

END

and then use this in the chart.

I also (for completeness) had to set a sort on this field (as it's no longer an actual date, but a string ...it will be sorted alphabetically, so this just ensures the dates remain in the right order)

Hope that does the trick and helps.

• ###### 2. Re: Dynamic X-axis based on the value in Parameter

Hi,

Find my approach,

little modification in your parameter, values in lower case so that it can be directly use in the calculation

To link this parameter in a calculated field write this logic

DATE(DATETRUNC([Period (copy)],[Order Date]))

Now check, drag the field and make it continuous

workbook attached for your reference. Let us know if this help.

Mahfooj

1 of 1 people found this helpful
• ###### 3. Re: Dynamic X-axis based on the value in Parameter

Hi Mahfooj,

Thanks for the help but the X-Axis does not change based on the Parameter value. If we select Quarter in the Paramter the x-axis should display Q1, Q2, Q3, Q4 instead of years or months.

Thanks

Jag

• ###### 4. Re: Dynamic X-axis based on the value in Parameter

Hi Simon,

The x-axis displays the desired result but when I compare the sum of Sales Target of all the Quarter in a year vs Year it does not match.

For Example: Q1 + Q2 + Q3 + Q4 for 2018 is not equal to Year 2018

Thanks

Jag

• ###### 5. Re: Dynamic X-axis based on the value in Parameter

I think that's because you alter the actual dates in your period formula

DATE(CASE [Period]

WHEN "Month" THEN Date(DATETRUNC('month',[Order Date]))

END)

If I select year (year of your period) 2018 and choose quarter get this

but if I select year I get

as you are adding 6 months to the actual date and then doing the date trunc on that date.

If I remove all the DATEADDs, it works consistently. Version with DATEADDS removed

• ###### 6. Re: Dynamic X-axis based on the value in Parameter

Simon, this is elegant! a very nice work around for a long standing frustration. Cheers!

Tom