5 Replies Latest reply on Jan 21, 2019 3:44 AM by Simon Runc

    Dynamic X-axis based on the value in Parameter

    Jagjit Singh

      Hi All,

       

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

       

       

      Appreciate your help please.

       

       

      Thanks

      Jag

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

          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
            Mahfooj Khan

            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
              Jagjit Singh

              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.

               

              Please check Simons's workbook.

               

              Thanks

              Jag

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

                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
                  Simon Runc

                  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]))

                  WHEN "Quarter" THEN Date(DATETRUNC('quarter',DATEADD('month',3,[Order Date]))-1)

                  WHEN "Year" THEN Date(DATETRUNC('year',DATEADD('month',6,[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