0 Replies Latest reply on Feb 28, 2016 11:06 AM by nickausaus

    Help with summarising data using multiple criteria

    nickausaus

      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?