How to display as 1 when GP% CY > LY ?

[paulyeo11]

Hi All

Now my Table 1 display correctly:-

SBU.....LY......CY

G3-SL...0.56....0.44

G4-CL...0.18....0.82

G1-GT...0.16....0.84

G2-AP...0.00....1.00

G5-RW...0.00....1.00

May i know how to make the above display as below , notice that when GP% CY > LY CY=1:-

SBU.....LY......CY

G3-SL...0.00....1.00

G4-CL...0.00....1.00

G1-GT...0.00....1.00

G2-AP...0.70....0.30

G5-RW...0.60....0.40

Thank

Paul

[Anil_Babu_Samineni]

Why last 2 rows become .7 and .6

[paulyeo11 · Accepted solution]

Hi Anil

Yesterday , take me 4 hour to figure , thank you very much , my solution as below :-

CY Expression :-

if( $(vGpVar) >1,1,$(vGpVar))

LY expression :-

1-if( $(vGpVar) >1,1,$(vGpVar))

vGpVar :-

(

(

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year)-0)}, sales = {'*'}>} month))"}>}sales)

-

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year)-0)}, sales = {'*'}>} month))"}>}cost)

)

/

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year)-0)}, sales = {'*'}>} month))"}>}sales)

)

/

(

(

Sum({$<year = {$(=Max(year)-1)}, month = {"<=$(=Max({<year={$(=Max(year)-1)}, sales = {'*'}>} month))"}>}sales)

-

Sum({$<year = {$(=Max(year)-1)}, month = {"<=$(=Max({<year={$(=Max(year)-1)}, sales = {'*'}>} month))"}>}cost)

)

/

Sum({$<year = {$(=Max(year)-1)}, month = {"<=$(=Max({<year={$(=Max(year)-1)}, sales = {'*'}>} month))"}>}sales)

)

Paul Yeo

[Anil_Babu_Samineni]

Great, you solve by ur self only. You can mark your response as correct answer to close this thread