Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Connect 2026! Turn data into bold moves, April 13 -15: Learn More!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

As on Date on RunTime

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

11 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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 

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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