Help..Customer with 3 Orders over 500 in the same year

[sultan123]

Thank you in advance..

I have situation where i need to write a note based on condition where

A Customer has placed at least 3 Orders over £500 in the same year.

GRouped by: Customer Name.

Detail
section: OrderID, OrderDate, OrderAmount, Running Total(for customer with order over 500)


Your help would be so much appreciated.

Many thanks

 

[lbass]

Assuming you are limiting your records to the year in question in your record select formula, you can create a formula {@>500}:

if {Orders.OrderAmount} > 500 then 1 else 0

Then create a second formula to display your comment in the groupb header or footer:

if sum({@>500),{Orders.CustomerID}) >= 3 then "Your Comment"

-LB

 

[sultan123]

Thank you Ibaas
I looks in lot better shape, how do i show within comment total number of orders which exceeded 500 condition in same year..

ie if customer A123 has placed 3 orders in 1998 and 5 in 1999 all exceeding 500 condition, 2 more orders in 1997 but less than 500 then

i need to say
at the end of customer A123 group
"Well done you have placed 8 orders each over £500. Allows 10% discount"

Meaning counting the number of order per customer meeting the condition, in the same formula?


many thanks

 

[sultan123]

Thank you Ibass
I looks in lot better shape, how do i show within comment total number of orders which exceeded 500 condition in same year..

ie if customer A123 has placed 3 orders in 1998 and 5 in 1999 all exceeding 500 condition, 2 more orders in 1997 but less than 500 then

i need to say
at the end of customer A123 group
"Well done you have placed 8 orders each over £500. Allows 10% discount"

Meaning counting the number of order per customer meeting the condition, in the same formula?


many thanks

 

[lbass]

Are you counting all orders regardless of year if they meet the 500 criterion? Or are you only counting those over 500 when there are at least three meeting the criterion within a calendar year? In your example above, if the two in 1997 had been greater than 500 would you have added them to the total which would then be 10? Also, do you want to display the number per year that meet the criterion or only the comment with the total?

To create the comment line, use a formula like:

if sum({@>500),{Orders.CustomerID}) >= 3 then "Well done! You have placed "+ totext(sum({@>500),{Orders.CustomerID}),0,"&quot + " orders each over £500. Allows 10% discount."

-LB

 

[Madawc]

I'd take a different approach. Add a running total for orders, but make it conditional on the amount being over £500. THen you have the total, to show or test in the footer.

Madawc Williams
East Anglia, Great Britain