3 Replies Latest reply: Feb 27, 2018 7:57 PM by Yeo Poh sai

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

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

• ###### Re: How to display as 1 when GP% CY > LY ?

Why last 2 rows become .7 and .6

• ###### Re: How to display as 1 when GP% CY > LY ?

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

• ###### Re: How to display as 1 when GP% CY > LY ?

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