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

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

    Yeo Poh sai


      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 ?
          Anil Samineni

          Why last 2 rows become .7 and .6

            • Re: How to display as 1 when GP% CY > LY ?
              Yeo Poh sai

              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