display date based on max sales

[nb4884]

Hello everyone,

Need help with building a formula. Need to display this in group footer. CR version XIR2

I have a column for sales in the form of dollars and a column for date MM/dd/yyyy hh:mm:ss.

I want to display a date when sales are maximum. I have grouped my report on Item ID ie for each item display the date when sales is maximum. Need to display date in MM/yy format. If there is a tie need to display both dates.

Thanks a lot

 

[MCuthill]

nb4884,

I believe that which you seek would be best found using a RunningEvaluative formula. You will need a set of formula fields for this:

{@VariableReset_MaxSalesDates}
(placed in the group header for each product)

WhilePrintingRecords;

Shared StringVar MaxSalesDates:="";
Shared NumberVar MaxSalesAmount:=0;

{@VariableEvaluation_MaxSalesDates}
(in the details row)

WhilePrintingRecords;

Shared StringVar MaxSalesDates;
Shared NumberVar MaxSalesAmount;

If {table.dollars}> MaxSalesAmount THEN
(
    MaxSalesDate:= ToText(Month({Table.Date}),00) & "/" & ToText(Year({Table.Date}),0000);
    MaxSalesAmount:={Table.dollars};
)
If {table.dollars}=MaxSalesAmount THEN
(
    MaxSalesDate:= MaxSalesDate & ", " & ToText(Month({Table.Date}),00) & "/" & ToText(Year({Table.Date}),0000)
    MaxSalesAmount:={Table.dollars};
)

and lastly a formula to display this/these result(s):
{@VariableDisplay_MaxSalesDates}
(place in group footer)

WhilePrintingRecords;

Shared StringVar MaxSalesDates;

This last formula should display the YYYY/MM with the most sales for each product in the footer for each product. Please note, I did not open crystal to write the above, there may be some syntax cleanup needed. [smiles]

If you were looking to display the details for the day with the max sales only, I am not certain how to proceed as the maximum day would only be known after evaluating. To you have one record per day per product for sales? Or is it transaction based and you are evaluating the total sales with a Group on Sale Date inside the product group? If the latter, the 2nd formula above would be placed in the inner group and need to be adjusted to look at the Sum({table.dollars},{table.date]) and compare to the variable instead of just comparing the transaction amount to the variable.

Hope this helps,

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

oops.

This last formula should display the YYYY/MM with the most sales for each product in the footer for each product.

should read:
This last formula should display the MM/YYYY with the most sales for each product in the footer for each product.

If you need just the last 2 numbers of the year...
change all: ToText(Year({Table.Date}),0000)
to: Right(ToText(Year({Table.Date}),0000),2)
Will result in MM/YY.

nb4884, you may also notice a surprising similarity to this solution and the question resolved by LBass from earlier this month...

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[nb4884]

Thanks a lot Mike, dint expct a quick response (smiles). I ll try the above suggestions in crystal (after holidays) and will post the results.

But thanks a lot!!

 

[MCuthill]

Happy to help out nb4884!

Have a Merry Christmas / Happy Holidays & let us fellow Tek-Tippers know how you make out with your report.



Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."