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!

[sunny_talwar]

I got very close with what you want not sure why B is 481. PFA the application

Have a look while I continue to troubleshoot.

Best,

S

[Gysbert_Wassenaar]

See attached qvw. I got a count of 2279 for A, not 2277 because invoices 269432 and 219219 have the same data for A and B.

[rubenmarin]

Hi, that's beacuse InvoiceNumber 219219 and 269432 has the same max Date for each Status.

Eleni, it should be debugged in data, probably adding time, or in the requirements specifying wich status should be counted when this happens, or if they should count in both status (Then the sum of each status will not sum the total of distinct invoice numbers).

[sunny_talwar]

Damn man, how did you figure that out? It would have taken me a long time to figure that out. What was your trick to figure it out?

[rubenmarin]

I used Gysbert solution, selected IsLatest='1'

Then I created a straigth tabe with Invoice_Number as dimension and Count(Date) as expression, sorting descing by expression there were this two invoices counting '2', all the other only count '1'

[sunny_talwar]

Sweet. I was thinking of troubleshooting it in Excel. It never crossed my mind to use QlikView itself to do that.

[jsakalis]

Please see attached.

[#9]

Hi guys,

Thank you all for your help.

The problem is that all these solutions works fine for one year.

What if we have an invoice with status A at 30/12/2013 and change to Status B at 2/1/2014?

If we are going to use max(Date) in the script we will get 2/1/2014 in field date although we select 2013 in year.

Let me know if you need more info.

Thanks in advance,

Eleni

[rubenmarin]

Hi Eleni, you can use:

Status A:

=Sum(Aggr(If(Max({<Invoice_Status={'A'}>} Date)=Max(Date), 1), Invoice_Number))

Status B:

Sum(Aggr(If(Max({<Invoice_Status={'B'}>} Date)=Max(Date), 1), Invoice_Number))