7 Replies Latest reply on Jan 21, 2019 12:10 PM by Joshua Preston

Move Max LOD to correction field segment

Hello - I have an LOD that returns the max measure for the max year, but it ends up on the wrong line.

It's fairly straightforward. Any ideas on how to get it to behave? thx. twbx 2018.3 attached.

example: the .022 percent of workers should be on the "At Minimum Wage" line, not the "Below Minimum wage line"

LOD:

if { FIXED [State]:

MAX({ FIXED [State],[Pay rate]: SUM([Pay Rate #])})}

={ FIXED [State],[Pay rate]: SUM([Pay Rate #])}

THEN [Max Pay 2017] END

• 1. Re: Move Max LOD to correction field segment

Joshua,

Not sure about the business logic you are trying to implement, but if this is what you are looking for

then you are missing the Year  Dimension in you lod

if { FIXED [State]:

MAX({ FIXED [State],[Pay rate],[Year]: SUM([Pay Rate #])})}

={ FIXED [State],[Pay rate],[Year]: SUM([Pay Rate #])}

THEN [Max Pay 2017] END

Michel

• 2. Re: Move Max LOD to correction field segment

Hey Michel - Sorry if I didn't specify. the max value largest value in 2017. the value needs to be at the end of the line with that max value.

• 3. Re: Move Max LOD to correction field segment

Like This?

Thanks

Deepak

• 4. Re: Move Max LOD to correction field segment

Like this ?

if [Pay Rate 2017] = [Max Pay 2017] then [Max Pay 2017] end

where  Pay Rate 2017  is

if Year = 2017 then [Pay Rate #] end

• 5. Re: Move Max LOD to correction field segment

Thanks Michel! I was able to work through your calcs and replicate them. This gets the label exactly where it's suppose to be. MUCH appreciated. If it's not too much trouble, can you explain the logic? I'm interested in understanding WHY this works along with having it work:) thx.

• 6. Re: Move Max LOD to correction field segment

if you look at Sheet 5  (and add missing discrete values)

You can see that you have correctly computed the Max Pay 2017 by State.

The new calculation Pay Rate 2017 returns the two Pay rates only for 2017.

And when we have equality between one of the two  Pay Rate 2017 and   the Max Pay 2017 we return the Pay rate   (Max Pay (Copy)).

So by fitering on another State, if the Max 2017 rate for this State is for the   Below Minimum Wage, then you will have the label on the other line. Arizona for example.

And you can see that your calculation was returning the value on all Below Minimum Wage, but the logic was not making much sense because it didn't take the year into account, so it was summing a bunch of Pay Rates which make no sense.

It's a good practice, before building the view, to bring all discrete values of whatever dimension and measure needed , on the Rows of a Sheet. From there it is easier to see what is happening and validate our calculations.

1 of 1 people found this helpful
• 7. Re: Move Max LOD to correction field segment

Thanks again Michel. This is very useful. I'm learning to use crosstabs to see all the discrete values as you recommended. I think that will help me lock down the logic. Thanks for taking the time to explain and the solution. cheers!