topic Re: Creating a Sumif formula based on Distinct field Values in QlikView

Creating a Sumif formula based on Distinct field Values

leale1997 — Wed, 25 Nov 2020 16:16:04 GMT

I feel like this should be easy but I am just missing something. I have tried aggr and sum and can't get this formula to work out correctly.

I have a data table that looks like the table below. I would like to calculate the number of days a record that is not closed has been opened based on today's date and Distinct record ID's. I have tried using variations of this formula:

sum(distinct if([Status]='Urgent' and isnull(DateClosed), today()-DateCreated, [RecordID]))

RecordID	DateCreated	Status	DateClosed
1	02/05/16	Urgent	02/25/16
2	01/01/17	Routine
3	07/05/16	Routine	08/04/16
4	09/10/16	Routine
5	02/10/16	Urgent
6	01/06/17	Routine
7	07/10/16	Routine
8	09/15/16	Routine
9	02/15/16	Routine	03/11/16
10	01/11/17	Routine
11	07/15/16	Routine
12	09/20/16	Routine	11/04/16
13	02/20/16	Urgent
14	01/16/17	Routine
15	07/20/16	Urgent	09/18/16
16	09/25/16	Urgent

Re: Creating a Sumif formula based on Distinct field Values

sunny_talwar — Wed, 29 Mar 2017 21:00:54 GMT

May be this:

Sum({<RecordID = {"=Len(Trim(DateClosed)) = 0"}, Status = {'Urgent'}>} Today() - DateCreated)

Re: Creating a Sumif formula based on Distinct field Values

leale1997 — Wed, 29 Mar 2017 21:35:58 GMT

Of course that's the answer ?#$%^&? LOL! Thanks so much!

Re: Creating a Sumif formula based on Distinct field Values

leale1997 — Thu, 30 Mar 2017 17:02:20 GMT

Hi Sunny!

You were such a help with my last question. Wonder if you might field another. I would like to do the same thing except leave out the second criteria.

So I think it would look like this:

Sum({} Today() - DateCreated)

But it’s not working. What am I missing?

Re: Creating a Sumif formula based on Distinct field Values

sunny_talwar — Thu, 30 Mar 2017 17:15:18 GMT

Leave which criteria? Closed Date or Status?

Re: Creating a Sumif formula based on Distinct field Values

leale1997 — Thu, 30 Mar 2017 17:25:44 GMT

Leaving closed date is null.

But not considering Status as a criteria at all.

In other words, summing today()-datecreated for distinct record ids that do not have a populated dateclosed field.

The record id field may contain duplicates.

Re: Creating a Sumif formula based on Distinct field Values

sunny_talwar — Thu, 30 Mar 2017 17:26:47 GMT

This

Sum({<RecordID = {"=Len(Trim(DateClosed)) = 0"}>} Today() - DateCreated)

Re: Creating a Sumif formula based on Distinct field Values

leale1997 — Thu, 30 Mar 2017 17:34:08 GMT

Yes that worked! Thanks so much. I realize now I left out the ‘>’.

Way appreciate the assist. )

Re: Creating a Sumif formula based on Distinct field Values

sunny_talwar — Thu, 30 Mar 2017 17:34:52 GMT

Sounds good