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

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 Name Document number Cal: Bucket Amount AAA 334 >7 Week 17,925 AAA 098 >6 Week 13,283 AAA 324 >7 Week 7,577 AAA 008 >9 Week 3,459 AAA 905 >9 Week 3,360 AAA 344 >8 Week 2,398 AAA 312 >7 Week 1,747 AAA 609 >7 Week 1,295 AAA 459 >6 Week 1,142 AAA 678 >6 Week 976 AAA 646 >8 Week 411 AAA 642 >8 Week 283 AAA 500 >6 Week 210 AAA 636 >7 Week 111 Total Quote Amount 54,177

>6 Week data segregated below.

 5002166334 >7 Week 17924.8 5002166324 >7 Week 7576.8 5002156008 >9 Week 3458.87 5002143905 >9 Week 3360 5002152344 >8 Week 2397.6 5002166312 >7 Week 1747.2 5002175609 >7 Week 1295.16 5002156646 >8 Week 410.7 5002152642 >8 Week 283.3 5002175636 >7 Week 111.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

• ###### 1. Re: Grouping greater than 6 weeks quotation amount and then divide it by total quote amount

Hey Ganesh,

Not being able to work with your data, what if you changed your "Buckets" calculated field, and for each grouping, return an integer instead of a text group (6 vs ">6 Week").

Then, you could create a calculated field like: (IF [Buckets] > 6 THEN SUM([Amount]) END) / SUM([Amount])

Walt

1 of 1 people found this helpful