topic Re: Field Count in QlikView

Field Count

Wed, 18 Mar 2015 12:15:31 GMT

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!

Re: Field Count

sunny_talwar — Wed, 18 Mar 2015 12:27:03 GMT

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,

Re: Field Count

Gysbert_Wassenaar — Wed, 18 Mar 2015 12:32:25 GMT

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.

Re: Field Count

rubenmarin — Wed, 18 Mar 2015 12:44:46 GMT

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).

Re: Field Count

sunny_talwar — Wed, 18 Mar 2015 12:47:40 GMT

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?

Re: Field Count

rubenmarin — Wed, 18 Mar 2015 12:50:40 GMT

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'

Re: Field Count

sunny_talwar — Wed, 18 Mar 2015 12:53:11 GMT

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

Re: Field Count

jsakalis — Wed, 18 Mar 2015 12:57:01 GMT

Please see attached.

Re: Field Count

Wed, 18 Mar 2015 14:35:49 GMT

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

Re: Field Count

rubenmarin — Wed, 18 Mar 2015 14:46:07 GMT

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))

Re: Field Count

Wed, 18 Mar 2015 15:07:30 GMT

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.).

Re: Field Count

jsakalis — Wed, 18 Mar 2015 15:15:33 GMT

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.

Re: Field Count

rubenmarin — Wed, 18 Mar 2015 15:17:58 GMT

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))

Re: Field Count

Thu, 19 Mar 2015 08:56:09 GMT

Hi John,

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

Any help?

Regards,

Eleni

Re: Field Count

Thu, 19 Mar 2015 11:23:28 GMT

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