4 Replies Latest reply on Feb 24, 2016 6:59 AM by Nasir Khan

# Parameter or Calculated Field for max qty/max date?

I am not sure if I need a Calculated Field or a Parameter here:

I am creating a dashboard for a Biggest Loser Challenge. I need the max value/% lost; but I need this number to change every week as the challenge progresses. I will be using his number several different ways; as a reference line, as a big number being displayed on a dashboard, in calculations etc. In the table below this for week 2 number would be -1.66% (Team 2) however for week 2 (when I refresh the data) this number will be -2.82% (Team 1).

*Every week I add new data while leaving the old data to show the progress on a timeline.

 Team # Team Week # Date % Loss 3 Team 1 2 2/5/2016 -1.57% 11 Team 2 2 2/5/2016 -1.66% 14 Team 3 2 2/5/2016 -1.58% 3 Team 1 3 2/12/2016 -2.82% 11 Team 2 3 2/12/2016 -1.21% 14 Team 3 3 2/12/2016 -1.87%

• ###### 1. Re: Parameter or Calculated Field for max qty/max date?

You can put LOD calculation.  // Overview: Level of Detail Expressions

[Max # by week]

{fixed [Week #]:min ([% Loss])}

[Max by all]

{fixed : min([Max # by week])}

You can put "Week#" into filter shelf and change it to "Context".

Then you can get historical view to have latest week, or at the point pf last week or so by filtering Week#.

Thanks,

Shin

9.0 attached.

• ###### 2. Re: Parameter or Calculated Field for max qty/max date?

Shinichrio,

Thanks for the quick reply. Can you write out the syntax for the max week, min value?

I rather do this with a syntax as I am using it with other parameters that I cannot use a date context for.

I am using it for example:

• ###### 3. Re: Parameter or Calculated Field for max qty/max date?

If this is exactly what your data looks like, try the below calculation:
MIN(IF [Date] = {MAX([Date])} THEN [% Loss] END)

If you have raw data at the individual level instead of at a team level, try this:

{INCLUDE [Team] :MIN(IF [Date] = {MAX([Date])} THEN [% Loss] END)}

• ###### 4. Re: Parameter or Calculated Field for max qty/max date?

Ben,

The formula MIN(IF [Date] = {MAX([Date])} THEN [% Loss] END) works on a team level. However if I want to show the team's % loss with the winner's % loss as a reference line, it does not work. This formula will show the individual team's % loss for the Max Date. What I want to be able to do is capture the MIN % loss for the Max Date and display that value as a reference line, as a target, use it to show difference needed to catch up with that team etc.