Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need your help for building a As on Date functionality for Few of my reports & i believe you all giuys can help me on the same.
Please see the attached Excel File as a Sample Data to be worked on.
In the attached excel you can see there is 1 single Receipt Number & have 3 Different Cash Rcpt ID's. (53000, 64001, 73000) now if i select a create a Autogenerated Date & select 17-Jul-2011 then i should search for all the GL Dates & give me the Max GL Date against each of the Cash Rcpt ID's & if the Status is 'REVERSED' for that specific row, then do not show the entire Cash Rcpt ID.
in this above case if i select 17-July-2011 then for
For Cash Rcpt ID :- 53000 max Date = 12-May-2011 & status <> 'REVERSED' hence it should get displayed
For Cash Rcpt ID :- 64001 Max Date = 07-Jul-2011 & Status = 'REVERSED' hence this whole Cash Rcpt ID (2 Rows) should not come in the report.
For Cash Rcpt ID :- 73000 Max Date = 04-Jul-2011 & Status <> 'REVERSED', hence it should come in the report.
2nd Scenario
If i select 6th-July-2011 (in As on Date filed Which is Autogenerated Date Filed but not the GL Date") -- Please Note 🙂
For Cash Rcpt ID :- 53000 max Date = 12-May-2011 & status <> 'REVERSED' hence it should get displayed
For Cash Rcpt ID :- 64001 Max Date = 20-Jan-2011 & Status <> 'REVERSED' hence this Record will come in the report for this as on Date (6th Jul-2011)
For Cash Rcpt ID :- 73000 Max Date = 04-Jul-2011 & Status <> 'REVERSED', hence it should come in the report.
Need all your help & suggestion..
Rgds,
Srini
HI,
As we discussed, here is what i have developed.
This works but the problem is it is developed using if statement, which is not good for large volume of data.
I am also working on solving this using set analysis.
Have a look at the attached file and do respond me, is this what you want?
Regards,
Kaushik Solanki
Hi Bhaskar & Kaushik,
Thanks for your Help Guys... 🙂 KUDO's..
I have used a Different Logic to arrive at the same using Aggr func()..& yes i will try out the best options as suggested by you both guys as per the Data Size..
Rgds,
Srini