topic Re: Count of Unique IDs based on two requirements (loop? if/then?) in QlikView

Count of Unique IDs based on two requirements (loop? if/then?)

Wed, 25 Nov 2020 16:16:04 GMT

Hello QlikView Users,

I am new to Qlikview (and this forum) and would kindly like to ask for your advice on the following question:

My data has the following structure:

DocumentID (unique) | Date start | Date end | …

My Task is to display the amount of "unfinished" DocumentIDs by 2016-calender week

A DocumentIDs is to be counted as "unfinished", if they were already started (Date start is filled), but do not have an Date end (is blank) yet.

I can easily get this by asking each start of the week how many DocumentIDs have a Date start, but not a Date end.

However, in doing so I cannot get the amount of unfinished DocumentIDs for the previous or older calender weeks (as they already have a Date end).

Thus, I would like to have a bar chart that shows me the COUNT of "unfinished" DocumentID by 2016-calender week.

A DocumentID should be counted for each 2016-calender week when it was startet, but not finished.

It should therefore be counted for each 2016-calender week that

Calendarweek(Date start) is < today() and
Calenderweek(Date end) is blank OR > today()

How can I realize this in Qlikview?

I assume I need a kind of loop that goes through all of my DocumentIDs for each 2016-calendar week and checks if the two requirements I have were met. If yes, it should be counted, if not it should be skipped and the loop should start the next 2016-calendar-week.

Thank you very much in advance for your help.

Kind regards,

Michael

Re: Count of Unique IDs based on two requirements (loop? if/then?)

sunny_talwar — Sat, 28 May 2016 12:06:11 GMT

Hi Michael -

If a document's end date is during the mid week, would that be considered as open or closed for that week? I have two solution proposed (And two more coming using IntervalMatch option)

Community_218635.qvw is including a document ending midweek into the open week. Script is as follows

Table:

LOAD Document,

Start,

If(Len(Trim(End)) > 0, End) as End,

Date(Start + IterNo() - 1) as OpenDate,

Dual(Week(Date(Start + IterNo() - 1))&'-'&Year(Date(Start + IterNo() - 1)), WeekStart(Date(Start + IterNo() - 1))) as OpenWeek

While Start + IterNo() - 1 <= If(Len(Trim(End)) > 0, End, Today());

LOAD * Inline [

Document, Start, End

A, 01/02/2016, 03/03/2016

B, 03/05/2016,

C, 05/23/2016,

D, 02/12/2016, 03/23/2016

];

Community_218635_v1.qvw is not including a document ending midweek into the open week. Script is as follows

Table:

LOAD Document,

Start,

If(Len(Trim(End)) > 0, End) as End,

Date(Start + IterNo() - 1) as OpenDate,

Dual(Week(Date(Start + IterNo() - 1))&'-'&Year(Date(Start + IterNo() - 1)), WeekStart(Date(Start + IterNo() - 1))) as OpenWeek

While Start + IterNo() - 1 < If(Len(Trim(End)) > 0, WeekStart(End), Today() + 1);

LOAD * Inline [

Document, Start, End

A, 01/02/2016, 03/03/2016

B, 03/05/2016,

C, 05/23/2016,

D, 02/12/2016, 03/23/2016

];

In the second script, you will see that A is not included in Week 10 because it is ending during the week 10.

Will be attaching IntervalMatch function soon.

Best,

Sunny

Re: Count of Unique IDs based on two requirements (loop? if/then?)

sunny_talwar — Sat, 28 May 2016 12:23:33 GMT

With IntervalMatch I was only able to replicate the first case. I am sure there is a way to do the second one also, but I am going to wait for your response to see if we even need that.

Table:

LOAD * Inline [

Document, Start, End

A, 01/02/2016, 03/03/2016

B, 03/05/2016,

C, 05/23/2016,

D, 02/12/2016, 03/23/2016

];

MinMax:

LOAD Min(Start) as Min,

Num(Today()) as Max

Resident Table;

LET vMin = Peek('Min');

LET vMax = Peek('Max');

TRACE $(vMin) & $(vMax);

DROP Table MinMax;

Calendar:

LOAD *,

Dual(Week(Date)&'-'&Year(Date), WeekStart(Date)) as OpenWeek;

LOAD Date($(vMin) + IterNo() - 1) as Date

AutoGenerate 1

While $(vMin) + IterNo() - 1 <= $(vMax);

IntervalMatch:

IntervalMatch(Date)

LOAD Start,

End as End

Resident Table;

Data structure with IntervalMatch:

Re: Count of Unique IDs based on two requirements (loop? if/then?)

Sat, 28 May 2016 15:56:54 GMT

Dear Sunny T,

first of all, thank you very much for your help.

Mid-Week Closure counts for the week where it was closed, not the following week.

(I always do my reporting on mondays - so whatever is open then is not counted).

For Open documents the current weeknumber (of today()) should be used as end date, therefore the open documents will always be added to the current week.

I transferred the code for the first solution to my Qlikview, slightly modified the second load to relfect my data, managed to load my Data from the Excel and created a Pivot Table to assess the count of documents by calenderweek.

LOAD Document, Start,

If(Len(Trim(End)) > 0, End) as End,

Date(Start + IterNo() - 1) as OpenDate,

Dual(Week(Date(Start + IterNo() - 1))&'-'&Year(Date(Start + IterNo() - 1)), WeekStart(Date(Start + IterNo() - 1))) as OpenWeek

While Start + IterNo() - 1 <= If(Len(Trim(End)) > 0, End, Today());

LOAD Document, Start, End FROM [..\sample.xlsx] (ooxml, embedded labels, table is Tabelle1);

However, utilizing my data, I do not come up with the numbers I recorded during the last weeks (by simply asking each start of the week how many documents were not finished).

The logic itself seems to work, because if I use a picklist filter to search for a Document ID, it states me all the weeks, when it was open. However somehow the counting is wrong.

Analyzing it in Excel, I come up with values roughly as follows:

CW18: 46

CW19: 49

CW20: 53

CW21: 56

Your solution gives the following values:

Openweek vs count(Document)

CW18: 352

CW19: 391

CW20: 397

CW21: 296

I assume I made an error with counting. If an ID is open for 2 weeks it seems to add 7 to each of the open weeks, instead of 1 for each week (it counts the days, not the weeks).

I assume I have to add some DISCTINT, but i dont know where 😕

Re: Count of Unique IDs based on two requirements (loop? if/then?)

sunny_talwar — Sat, 28 May 2016 18:13:05 GMT

Would you be able to share your application or the data behind the application? I might not know what isn't working without taking a look at it.

Best,

Sunny

Re: Count of Unique IDs based on two requirements (loop? if/then?)

Sat, 28 May 2016 20:29:09 GMT

Actually, I solved it by playing around a bit

For the Graph I needed to use:

count(DISTINCT Document)

Now it perfectly works.

Thank you very much!

Topic can be closed.

Re: Count of Unique IDs based on two requirements (loop? if/then?)

sunny_talwar — Sat, 28 May 2016 23:18:13 GMT

Awesome