Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
With the below tables I simply want to create a text box and a straight table that will show the billed units after the contract start date. e.g. account 123456
contract started on 01/05/11 and the bill was on 30/06/11 therefore I want to see their units in the text box and straight table. It becomes more difficult when there are multiple accounts and various different bill dates and contract start dates. So basically from the below data I want to see in a text box and straight table that there are 48,600 units that were billed after their contract started. Any ideas are welcome.
Account | Bill Date | Units |
---|---|---|
123456 | 30/06/11 | 3000 |
234567 | 30/05/11 | 3500 |
345678 | 30/06/11 | 2500 |
456789 | 30/04/11 | 4000 |
567890 | 30/05/11 | 5600 |
678901 | 30/06/11 | 3500 |
789012 | 30/04/11 | 40000 |
Account | Contract Start Date |
---|---|
123456 | 01/05/11 |
234567 | 01/06/11 |
345678 | 01/07/11 |
456789 | 01/06/11 |
567890 | 01/05/11 |
678901 | 01/07/11 |
789012 | 01/03/11 |
Thanks,
Ralph
Maybe this?
sum(aggr(sum(if("Bill Date">"Contract Start Date","Units")),"Account"))