
1. Re: Move Max LOD to correction field segment
Michel Caissie Jan 21, 2019 10:09 AM (in response to Joshua Preston)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

payrate_test(mc).twbx 70.1 KB


2. Re: Move Max LOD to correction field segment
Joshua Preston Jan 21, 2019 10:22 AM (in response to Michel Caissie) 
3. Re: Move Max LOD to correction field segment
Deepak Rai Jan 21, 2019 10:44 AM (in response to Joshua Preston)
payrate_test(mc)_v10.5.twbx 70.2 KB


4. Re: Move Max LOD to correction field segment
Michel Caissie Jan 21, 2019 10:46 AM (in response to Joshua Preston)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

payrate_test(mc2).twbx 65.4 KB


5. Re: Move Max LOD to correction field segment
Joshua Preston Jan 21, 2019 11:06 AM (in response to Michel Caissie)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
Michel Caissie Jan 21, 2019 11:38 AM (in response to Joshua Preston)1 of 1 people found this helpfulif 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.

7. Re: Move Max LOD to correction field segment
Joshua Preston Jan 21, 2019 12:10 PM (in response to Michel Caissie)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!