Former Member

Return Last value to Group Footer where Detail Date is less than Footer/Header Date

Hi All

I am trying to return a value from the detail section to the group footer where the value returned is less than the date that is in the detail.

Detail Section

Sale Date Revenue Cost Product 1/1/2016 \$4000 \$1000 Red Car 1/4/2016 \$3500 \$500 Blue Car 5/5/2016 \$2300 \$300 Yellow Car 7/5/2016 \$1202 \$109 Orange Car

Group Footer (What i am trying to return)

Current Date Last Sale Date Prior Product Revenue 5/5/2016 1/4/2016 Blue Car \$3500

basically in the details Data i have is newer data that is required in the footer therefore using a simple formula to return the last value is no good as it is returning the sale from the 7/5/2016. As the Current date is 5/5/2016 i want to return the product and revenue which is from the Last Sale Date 1/4/2016.

Any help would be great.

kind Regards

dv

10|10000 characters needed characters exceeded

Related questions

Posted on Jul 18, 2016 at 11:33 AM

Hi David,

How did you derive 5/5/2016 to be the current date?

-Abhilash

10|10000 characters needed characters exceeded
• Abhilash Kumar Former Member

Hi David,

You'd need a formula for each column.

Here's an example for the 'Last Sale Date Prior' formula:

1) Create a formula @LastSaleDate with this code and place this on the Details:

shared numbervar x := x + 1;

If {Sale Date} = {?Current Date Prompt} then

shared datevar LastSaleDate := Previous({Sale Date});

'';

2) Create a formula called @DispLastSale and place this on the Group Footer:

shared numbervar x;

shared datevar LastSaleDate;

if x = 1 then {Sale Date}

else LastSaleDate;

3) Create one last formula to reset the values and place this on the Group Footer:

shared numbervar x := 0;

shared datevar LastSaleDate := cdate(0,0,0);

Re-create formulas for other columns in the same manner with matching datatypes as the columns being used.

Also, don't include the x := x + 1 line in other formulas.

E.g formula for Product would be:

If {Sale Date} = {?Current Date Prompt} then

shared stringvar Product:= Previous({Product});

'';

And the corresponding display formula on the footer would be:

shared numbervar x;

shared stringvar Product;

if x = 1 then {Product}

else Product;

You can include all variables in the same reset formula on the Group Header like so:

shared numbervar x := 0;

shared datevar LastSaleDate := cdate(0,0,0);

shared stringvar Product := '';

shared numbervar revenue := 0;

-Abhilash