1 Reply Latest reply on Dec 13, 2016 7:52 AM by Walt Reed

    Grouping greater than 6 weeks quotation amount and then divide it by total quote amount

    Ganesh Vijay Kumar

      Hello Experts,

       

      I am trying to create calculated field on Quotation data.

       

      1) I created a calculated field to calculate Number of days.

      TODAY()-[Create Date]

       

      2) I created Buckets using below mentioned formula:

      IF [Cal: Create Date Diff in Days] >= 1 AND [Cal: Create Date Diff in Days] <= 7 THEN

          "<1 Week"

      ELSEIF [Cal: Create Date Diff in Days] >= 8 AND [Cal: Create Date Diff in Days] <= 14 THEN

          ">1 Week"

      ELSEIF [Cal: Create Date Diff in Days] >= 15 AND [Cal: Create Date Diff in Days] <= 21 THEN

          ">2 Week"

      ELSEIF [Cal: Create Date Diff in Days] >= 22 AND [Cal: Create Date Diff in Days] <= 28 THEN

          ">3 Week"

      ELSEIF [Cal: Create Date Diff in Days] >= 29 AND [Cal: Create Date Diff in Days] <= 35 THEN

          ">4 Week"

      ELSEIF [Cal: Create Date Diff in Days] >= 36 AND [Cal: Create Date Diff in Days] <= 42 THEN

          ">5 Week"

      ELSEIF [Cal: Create Date Diff in Days] >= 43 AND [Cal: Create Date Diff in Days] <= 49 THEN

          ">6 Week"

      ELSEIF [Cal: Create Date Diff in Days] >= 50 AND [Cal: Create Date Diff in Days] <= 56 THEN

          ">7 Week"

      ELSEIF [Cal: Create Date Diff in Days] >= 57 AND [Cal: Create Date Diff in Days] <= 63 THEN

          ">8 Week"

      ELSEIF [Cal: Create Date Diff in Days] > 63 THEN

          ">9 Week"

      END

       

      I would like to calculate another calculated field mentioned below to calculate percentages and stuck in between and any expert advise will be really grateful.

       

      Quotes > 6 Week amount/ Total Quote Amount

       

      Below is the data of one of the customer:

       

          

      Worldwide Customer NameDocument numberCal: BucketAmount
      AAA334>7 Week17,925
      AAA098>6 Week13,283
      AAA324>7 Week7,577
      AAA008>9 Week3,459
      AAA905>9 Week3,360
      AAA344>8 Week2,398
      AAA312>7 Week1,747
      AAA609>7 Week1,295
      AAA459>6 Week1,142
      AAA678>6 Week976
      AAA646>8 Week411
      AAA642>8 Week283
      AAA500>6 Week210
      AAA636>7 Week111
      Total Quote Amount54,177

       

      >6 Week data segregated below.  

      5002166334>7 Week17924.8
      5002166324>7 Week7576.8
      5002156008>9 Week3458.87
      5002143905>9 Week3360
      5002152344>8 Week2397.6
      5002166312>7 Week1747.2
      5002175609>7 Week1295.16
      5002156646>8 Week410.7
      5002152642>8 Week283.3
      5002175636>7 Week111.45
      Quotes > 6 Week amount 38,565.88

       

        

      Quotes > 6 Week amount 38,565.88
      Total Quote Amount 54,177.08

       

      Quotes > 6 Week amount/Total Quote Amount

      38,565.88 / 54,177.08 = 71.18%

       

      It would be great if anyone could help me to create a calculated field grouping >6 weeks data.

       

      Thanks,

      Ganesh