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

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

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

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
• ###### 3. Re: Dynamically compare two values based on user selection without using parameter

This is exactly what I am looking for.