6 Replies Latest reply on Aug 15, 2016 11:45 AM by Changan He

    Create a view show quarterly and yearly sales.

    Changan He

      Hi All,

      Here is the question,

       

      I can create a parameter and calculation field to display two view in one worksheet. But I can not properly present the hierarchy.

      The calculation I've created is

       

      Case [Parameter 1]

      When 'qoq' Then DATEPART('quarter',[Order Date])

      When 'yoy' Then DATEPART('year', [Order Date])

      END

       

      What I want to do is like

      Case [Parameter 1]

      When 'qoq' Then DATEPART('quarter',[Order Date]),DATEPART('year', [Order Date])

      When 'yoy' Then DATEPART('year', [Order Date]),DATEPART('quarter',[Order Date])

      END

      But this Calculated field is wrong.I can not display 2 dateparts in 1 condition.

       

      I appreciate for any help.

       

      Thanks,

      Mike.

        • 1. Re: Create a view show quarterly and yearly sales.
          lei.chen.0

          Hello Changan,

           

          These parts of your calculation are not valid.

           

          DATEPART('quarter',[Order Date]),DATEPART('year', [Order Date])

          DATEPART('year', [Order Date]),DATEPART('quarter',[Order Date])

           

          it's Number, Number .

           

          If you want to concatenate them, please use something like,

          STR(DATEPART('quarter',[Order Date])) + STR(DATEPART('year', [Order Date]))

           

           

          Regards

          1 of 1 people found this helpful
          • 2. Re: Create a view show quarterly and yearly sales.
            Changan He

            Hi Chen,

            Thanks for response.

             

            But after I concatenated them ,the qoq and yoy in parameter displayed the same result.Do you have any suggestion for this case?

             

            Best,

            Changan He

            • 3. Re: Create a view show quarterly and yearly sales.
              pulkit jain

              I am not sure if I got your requirement right but I guess it goes like this:

               

              you want to change the order of quarter and year in the chart based on user selection.

              i.e in one option you want fist partition by year then quarter, and in other option you want quarter first and then year. If that is correct below is one solution think might work.

               

              created to calculated field as:

              1. Top
              2. Bottom

               

              top calculation as:

              Case [Parameter 1]

              When 'qoq' Then DATEPART('quarter',[Order Date])

              When 'yoy' Then DATEPART('year', [Order Date])

              END

               

              bottom calculation as:

              Case [Parameter 1]

              When 'qoq' Then DATEPART('year', [Order Date])

              When 'yoy' Then DATEPART('quarter',[Order Date])

              END

               

               

              now put both top and bottom calculation in you column/row shelf instead of directly pulling year or quarter.

              therefore when user will change parameter the values in bottom and top calculation will adjust the hierarchy.

               

              Let me know if it helped.

               

              Regards,

              Pulkit Jain

              • 4. Re: Create a view show quarterly and yearly sales.
                lei.chen.0

                Hello Changan,

                 

                Please check if your calculation field is,

                 

                Case [Parameter 1]

                When 'qoq' Then STR(DATEPART('quarter',[Order Date]))+"/"+STR(DATEPART('year', [Order Date]))

                When 'yoy' Then STR(DATEPART('year', [Order Date]))+"/"+STR(DATEPART('quarter',[Order Date]))

                END

                 

                I added "/" to make it easy to recognize year and quarter.

                 

                But according to your screenshot, JAIN's solution is correct.

                 

                By concatenating year and quarter, there's only one dimension on the viz.

                Actually, two dimensions appear in your screenshot, as described in JAIN's solution as "Top" and "Bottom".

                 

                yoy

                qoq

                 

                 

                Regards.

                • 5. Re: Create a view show quarterly and yearly sales.
                  Changan He

                  Hi Jain,

                  Thanks for help!!! I got the hierarchy by using two calculated fields.:D

                   

                  Best,

                  Changan He

                  1 of 1 people found this helpful
                  • 6. Re: Create a view show quarterly and yearly sales.
                    Changan He

                    Hi Chen,

                    Thanks for the help.It is not the visual I want.But now I know how to concatenate in calculation field.

                     

                    Best,

                    Changan He