Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Comparing dates and other fields

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.

AccountBill DateUnits
12345630/06/113000
23456730/05/113500
34567830/06/112500
45678930/04/114000
56789030/05/115600
67890130/06/113500
78901230/04/1140000

AccountContract Start Date
12345601/05/11
23456701/06/11
34567801/07/11
45678901/06/11
56789001/05/11
67890101/07/11
78901201/03/11

Thanks,

Ralph

Tags (2)
1 Reply
MVP
MVP

Re: Comparing dates and other fields

Maybe this?

sum(aggr(sum(if("Bill Date">"Contract Start Date","Units")),"Account"))

Community Browser