4 Replies Latest reply on Jan 5, 2017 1:53 AM by ryan.shukis

    Using a Parameter within a Calculated Field

    ryan.shukis

      Right now I have this:

       

      • Parameter to control whether Dollars or Units are shown called "Dollars Units"
      • Formula for the individual year units: IF YEAR([Date]) = 2013 THEN [Units] END - - > works fine
      • Calculated field for the parameter that controls dollars/units - - > works fine if I just place the standard date pill in the column dimension but DOES NOT work if I try to use the parameter within the calculated 2013 field.
        • CASE [Dollars Units]

        • WHEN 'Units' THEN SUM([Units])

        • WHEN 'Dollars' THEN SUM([Sales])

        • END

      • Desired Formula: IF YEAR([Date]) = 2013 THEN [Dollars Units] END - - > however no values appear, just "ABC" when I place it in the measures (after being in dimensions)

        • I have the individual year to show the units since the quick table calculations always add that blank column which is beyond annoying.
        • So here i would like for the units or dollars to appear (based on parameter selection) for only the year 2013, is this possible ?

       

      Thank you in advance for any help!

        • 1. Re: Using a Parameter within a Calculated Field
          Sarah Ebreo

          Hi Ryan,

           

          Try replacing [Dollars Units] in the desired formula with the name of the calculated field in your third point. You may need to aggregate the YEAR([Date]) as well by using the ATTR function. Does the following calculation work?

           

          IF ATTR(YEAR([Date])) = 2013 THEN [Calculated Field] END

           

          Where [Calculated Field] is defined as:

          CASE [Dollars Units]

          WHEN 'Units' THEN SUM([Quantity])

          WHEN 'Dollars' THEN SUM([Sales])

          END

           

          Or you could write it in a single calculated field as:

           

          IF ATTR(YEAR([Order Date])) = 2013 THEN

          (CASE [Dollars Units]

          WHEN 'Units' THEN SUM([Quantity])

          WHEN 'Dollars' THEN SUM([Sales])

          END)

          END

           

          Best,
          Sarah

          • 2. Re: Using a Parameter within a Calculated Field
            ryan.shukis

            Hi Sarah!

             

            Thank you! It almost works.  The only remaining part is that when I pull this new calculated field over to the measures area of the table, no field label or numbers display.  So the current field label is " '13 " but it doesn't show up.  However this new field does work if I pull over the "Date" dimension into column labels, then the numbers show up.

             

            IF ATTR(YEAR([Date])) = 2013 THEN

                    (CASE [Dollars Units]

                 WHEN 'Units' THEN SUM([Units])

                 WHEN 'Dollars' THEN SUM([Sales])

            END)

            END

             

            Example.PNG

            • 3. Re: Using a Parameter within a Calculated Field
              Sarah Ebreo

              Hey Ryan,


              I removed the aggregation from the calculated field and was able to get the data to without Date on Columns. Try the following calculation and let me know if it works for you:

               

              IF YEAR([Order Date]) = 2013 THEN

                      (CASE [Dollars Units]

                   WHEN 'Units' THEN [Units]

                   WHEN 'Dollars' THEN [Sales]

              END)

              END

               

              Sarah

              • 4. Re: Using a Parameter within a Calculated Field
                ryan.shukis

                Thank you so much Sarah!  This worked perfectly!