How to make amount zero based on Minimum date

[former_member205840]

Hi,

Below is my requirement, I need to find minimum date in type group where group name equal to 'aaa' then compare rest of Transaction group dates, if any date is less than this date then make amount zero.

Ex: in 101 transaction minimum date for type 'aaa' is 01/01/2010 and when compare this date with rest of dates 01/01/2009 is less than this date so, my amount should be zero.

Transaction	type	date	amount	required results
101	aaa	01/01/2010	5678	5678
101	aaa	01/10/2011	5464	5464
101	def	05/08/2010	9876	9876
101	dez	01/01/2009	5656	0
101	dex	05/01/2011	7658	7658
102	aaa	01/10/2011	6785	6785
102	aaa	01/01/2009	4567	0
102	xdw	11/12/2008	3234	3234
102	dfe	10/12/2010	2123	2123
102	xsd	03/12/2011	4563	4563

I can do this through sub report, but due to huge volume of date this report is running for 10 hrs.and it is not acceptable.  Need to do this in single report.

Thanks in advance for any suggestions.

-Sastry

[abhilash_kumar · Accepted solution]

Hi Sastry,

Just a clarification, isn't 01/01/2009 the minimum date for 'aaa'?

Or, do you want the date comparison to be based on the 'first occurrence of aaa'?

-Abhilash

[former_member205840 · Accepted solution]

Hi Abhilash,

To give more clarity, my first group is Transaction and second group is type.  In my second group need to find minimum date for value 'aaa' and compare with rest of my first group dates.  If any date is less than this date then amount should be zero.

Thanks,

Sastry

[abhilash_kumar · Accepted solution]

Hi Sastry,

Here's what you need to do:

1) Create a formula with this code and place this on the Report Header:

shared datevar array dd := cdate(0,0,0);

'';

2) Create another formula and place this on the Details:

shared datevar array dd;

if dd[1] = cdate(0,0,0) then

(

if {Type} = 'aaa' then

(

    numbervar x := x + 1;

    redim preserve dd[x];

    dd[x] := date(Minimum({Date},{Type}));

);

);

'';

3) Create another formula and place this on the Details. This is the formula to display the amounts:

evaluateafter({@test2});

shared datevar array dd;

if {Type} = 'aaa' and {Date} < dd[1] then

0 else {Amount}

I hope I interpreted your question correctly.

-Abhilash

[former_member205840 · Accepted solution]

Hi Abhilash,

Sorry for giving wrong example, below is the correct example.

Transaction	type	date	amount	required results
101	aaa	01/01/2010	5678	5678
101	aaa	01/10/2011	5464	5464
101	def	05/08/2010	9876	9876
101	dez	01/01/2009	5656	0
101	dex	05/01/2011	7658	7658
102	aaa	01/10/2011	6785	6785
102	aaa	01/01/2009	4567	4567
102	xdw	11/12/2008	3234	0
102	dfe	10/12/2010	2123	2123
102	xsd	03/12/2011	4563	4563

Transaction Group--- > Typegroup--> from this type group I need to pickup minimum date for type='aaa' and within Transaction group if any date is less than this date then amount should be zero.

Here 101 Transaction gruop--01/01/2010 is minimum date for type group and within same Transaction group 01/01/2009 is less than 01/01/2010 so, amount is zero.

Like wise second Transaction group 102

Thanks for understanding,

Sastry

[abhilash_kumar · Accepted solution]

OK, that should be even easier then:

1) Create a formula (@Min) with this code:

If {type} = 'aaa' then {date}

2) Create another formula with this code for the Amounts:

If Minimum({@Min}, {Transaction Group}) > cdate(0,0,0) AND

{Date} < Minimum({@Min}, {Transaction Group})

then 0 else {Amount}

-Abhilash