I've attached a simple spreadsheet to highlight an issue I need resolved. Basically what the spreadsheet shows is 4 accounts and their statuses over time. I want to have a bar chart that will show the count of distinct accounts (with the date as a dimension) that have a current/most recent status of Pending, i.e. I don't want to highlight accounts that had a status of pending in the past or have a current status of cancelled or complete, just simply the most recent dates and the count. From the data attached 3 of the accounts would satisfy this criteria (account 34567 would not as its current/most recent status is Complete). So the x axis would have the dates 01/02/2012 (Account 45678), 04/03/2012 (Account 67890) and 04/04/2012 (Account 12345) and all dates would just have a count of 1.
I used the expression if(firstsortedvalue(Status='Pending',-Date),count (Account)) but this doesn't work. Any suggestions would be great and please ask if you need any clarification.
