Help with summarising data using multiple criteria

Discussion created by nickausaus on Feb 28, 2016

Hi all

I've been struggling with this overnight so thought its time to reach out.

I am working with 3 tables:

1. Asset information

Asset ID, IN Service Date, End Service Date

2. Transactional information such as

Asset ID, Month, Production, Receipts (by month)

3. Summary

Month, Total Production, Total Receipts (for all assets).


No trouble with YTD and total year figures by creating relationships between Transactions Table and Summary (based on Month, Category, Year)

What I can't work out, is how to exclude those figures in the Transaction table, where 'Month' is >= End Service Date. There should be no information for these assets but i prefer to do it this way if possible.

I've tried all sorts of combinations in the table relationships (Month >= IN Service Date, Month <= End Service Date) and lots of others but not getting the required result. I am able to effectively hide the results in my Portal using something like:

     and Assets::In Service Date<=Transaction Data::Month

     and Assets::End Service Date>=Transaction Data::Month


Any ideas much appreciated. I'm starting to think I need to implement the date validity in calculation rather than table relationships?