4 Replies Latest reply on Jun 16, 2019 8:33 AM by Vivek Br

    calculated field on Color marks

    Vivek Br

      Hello All,

      i have created a calculated field as shown below to compare the quarters with a parameter and when i drag this calculated field to color marks i'm not getting all values.

       

      calculated field as below,

       

      IF DATEDIFF('quarter',[Order Date],[Month and Year])=0

      THEN 'Current'

      ELSEIF  DATEDIFF('quarter',[Order Date],[Month and Year])=1

      THEN 'Previous'

      ELSEIF DATEDIFF('quarter',[Order Date],[Month and Year])=4

      THEN 'Previous Year'

      ELSE 'Older'

      END

       

      Note: Month and Year is a parameter created from Order Date.

       

      Expecting to display Current, Previous, Previous Year and Older.

      But

      Result on viz Current and Older.

       

       

      is there any calculations i'm doing wrong?

      kindly help me on this.

      Dataset : Superstore

       

      Thank You

        • 1. Re: calculated field on Color marks
          Jim Dehner

          Hi

          I'm not certain what you wanted but this is what you have

          when I opened th book the parameter was set to the year 2013 so you would only get the 4Q on 2013 but noting the year before - and a total for Other

           

           

          if the goal want to just show Q4 numbers  - ---   the other calc is just gett all the data that is not included in the  first 3 clauses - you would need to use a combined conditional with an AND to include ONLY Q4 but not include the current Q4 or the Previous Q4   -  

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: calculated field on Color marks
            Vivek Br

            Hello Jim,

            First of all, thank you so much this helped to understand a bit. as i'm new to this can you explain me bit more how this worked.

            If possible.

             

            Thank you

            • 3. Re: calculated field on Color marks
              Jim Dehner

              sure

              this is your formula

               

              IF DATEDIFF('quarter',[Order Date],[Month and Year])=0

              THEN 'Current'

               

              ELSEIF  DATEDIFF('quarter',[Order Date],[Month and Year])=1

              THEN 'Previous'

               

              ELSEIF DATEDIFF('quarter',[Order Date],[Month and Year])=4

              THEN 'Previous Year'

               

              ELSE 'Older'

              END

               

              Then month year is your parameter which sets the Max date to be used in the first 3 clauses of the If then statement

              the first clause just looks at the current year ( current being the year of the date in the parameter ) and the quarter in the parameter and calls the Current

              the second clause says to look bach 1 quarter and call the data in that quarter Previous

              the third clause says look back 4 quarters and take the data in that quarter and call it previous year

               

              now any data that did not fall into the first 3 clauses will be called Older

               

              if you wanted to just work with the single quarter in the parameter you could use

               

              if datepart('quarter',[order date] ) = datepart('quarter',[month and year])   AND

              datepart('year',[order date] ) = datepart('year',[month and year])  then "Current"

              elseif

              if datepart('quarter',[order date] ) = datepart('quarter',[month and year])   AND

              datepart('year',[order date] ) = datepart('year',  dateadd('year'-1, [month and year]))  then "Previous Year"

              elseif

              if datepart('quarter',[order date] ) = datepart('quarter',[month and year])   AND

              datepart('year',[order date] ) < datepart('year',  dateadd('year'-1, [month and year]))  then "Older"

              elseif

              if datepart('quarter',[order date] ) = datepart('quarter',dateadd('quarter',-1,     [month and year])  ) then "Previous Quarter"

               

              end

               

              Thi will Returns Nulls for any quarters other than the one in the parameter date

               

              you will have to check the syntax - I was not able to test the fromula

              Jim

              1 of 1 people found this helpful
              • 4. Re: calculated field on Color marks
                Vivek Br

                Thanks a lot Jim,

                Now understood how parameter and my calculated field worked in and why i was not getting Previous Year when my parameter was 2013. Basic calculation i was aware.

                 

                Thank you once again.