Skip to Content
author's profile photo Former Member
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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Jul 18, 2016 at 11:33 AM

    Hi David,

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

    -Abhilash

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.