3 Replies Latest reply on Jun 9, 2016 2:54 PM by Yi-chun Chou

    Dynamically compare two values based on user selection without using parameter

    Yi-chun Chou

      I need to compare values based user's selection of "As Of Date" field.   I cannot use parameters because data is constantly being update.

       

      Here is a markup what I am trying to achieve.   User will filter the list by Client first and then choose two "As of Date" to compare.

       

       

      Here is the file that I tried to start.  Please let me know if this is possible.

        • 1. Re: Dynamically compare two values based on user selection without using parameter
          Joe Oppelt

          Here's a stab at what I think you are looking for.  See attached.

           

          If this is what you need, I can describe what I did in there.

          • 2. Re: Dynamically compare two values based on user selection without using parameter
            Joshua Milligan

            Yi-Chun,

             

            How about using a filter instead?  That way the list of values is dynamic.  I was able to create a view that only shows when exactly two dates are selected via filter and they show the difference as you specify:

             

             

            It's a little complicated, but not too bad.  The basics are:

             

            1. Create a calculated field to filter the view and prevent it from showing unless exactly two dates are selected:

            [Exactly 2 dates]

            TOTAL(COUNTD([As Of Date])) == 2

             

            It is a table calculation that needs to be computed along all dimensions in the view and used as a filter where the value is True (or exclude False):

             

             

            2. Create another series of calculations to determine the Max Date selected, Min Date selected, the values at those dates, and then a final calculation to show the value in the table, but the Difference for the Row Grand Totals:

             

            [Min Date]

            {EXCLUDE [As Of Date],[Type] : MIN([As Of Date])}

             

            [Max Date]

            {EXCLUDE [As Of Date],[Type] : MAX([As Of Date])}

             

            These LOD calcs exclude the dimensions in the view and get the min or max date overall.

             

            [Value of Max Date]

            IF [As Of Date] == [Max Date] THEN [Total] END

             

            [Value of Min Date]

            IF [As Of Date] == [Min Date] THEN [Total] END

             

            These calcs are done row-by-row (row level) and simply compare the value of As Of Date to the overall Min or Max

             

            [Value]

            //If the user has selected exactly 2 dates (and only in the grand total, because in each column, the count will be 1)

            IF MIN([As Of Date]) <> MAX([As Of Date])

            THEN SUM([Value of Max Date]) - SUM([Value of Min Date])

            //Otherwise, just show the total

            ELSE SUM([Total])

            END

             

             

            Value is the field that you'll use to show the value.  The IF condition checks to see if the min and max of As Of Date are different.  They will only be different in the Row grand total, because in each column, there is only one As Of Date (so min and max will be the same).

             

            If it is in the Row grand total, then we take the difference between the max value and min value.  Otherwise, we simply take the total.

             

            I've attached the workbook so you can see it in action.  Hope that helps!

             

            Best Regards,

            Joshua

            1 of 1 people found this helpful