Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Why last 2 rows become .7 and .6
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
Great, you solve by ur self only. You can mark your response as correct answer to close this thread