Field Count

[#1]

Hello,

I have three fields. Invoice_number, status and date. An invoice_number can change status during time. That means it can change from status A to status B and then to status A etc. This can happen many times during a period of a year. My goal is to create three text objects, one with the total number of invoices, the other with  total number of invoices in Status A and the last one with  total number of invoices in Status B.

The problem is that we have to take into account only the last change of status.  For example the invoice_number 52578 has to count it only one at status A date 5/8/2014.

Please see attached.

Any help would be appreciated.

Thank you!

[#2]

Ruben,

That's good but it's not working properly when you make a status selection.

The ideal senario is to have a single text object which is going to show Total Invoices without Selections, Status A invoices with selection A in field status and Status B invoices with selection B in field status. And of course it has to works fine for any other selection (e.g. year, quarter, month e.t.c.).

[jsakalis · Accepted solution]

Hi Eleni,

    If I understood, you are interested in knowing the last status change that occurs in any year for a given Invoice_Number.

    See attached. Some changes were made to simulate 2013 records and group by to also use year.

    Filtering on year will give numbers specific to that year, based on last status change.

[rubenmarin]

Hi eleni,

I'm not sure of wch selections should work and wich not, can this give the desired result?:

='Total count:'& count({<Invoice_Status>} distinct Invoice_Number)

& Chr(13) & 'Count Status A: ' & sum({1}Aggr(If(Max({<Invoice_Status={'A'}>} Date)=Max({<Invoice_Status>} Date), 1), Invoice_Number))

& Chr(13) & 'Count Status B: ' & sum({1}Aggr(If(Max({<Invoice_Status={'B'}>} Date)=Max({<Invoice_Status>} Date), 1), Invoice_Number))

[#5]

Hi John,

Your solution works fine when the invoice has no lines, but if there are lines it doesn't.

Any help?

Regards,

Eleni

[#6]

Hi Guys,

Thank you all for your help and ideas were more than valuable!

I finally resolve it based on John's idea.

Cheers,

Eleni