Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table of records, each having a "Created on" and a "Closed on" date field. I also have a second table that maps a date to the corresponding work week.
Currently I have created a chart with the work weeks as dimension and the number of records created in that work week as expression, by joining the two tables on the Created on date and using
Sum(RecordCounter)
(which is just the number 1 for each record) as the expression definition.
Now my goal is to add a second expression for the total number of records closed on the respective work week. If I do another join with the master calendar on the Closed on date, I would have two different work week fields, but I need to have just one. Can you please point me in the right direction on how that can be achieved?
Many thanks in advance.
Use the above data model, but load Table2 the following way
Load WorkItemID, Date,
if(Date=CreatedOn, 1, 0) as IsCreatedOn,
if(Date=ClosedOn, 1, 0) as IsClosedOn;
Load WorkItemID, ClosedOn, CreatedOn,
Date(CreatedOn+IterNo()-1) as Date
resident Table1
While IterNo() <= ClosedOn - CreatedOn + 1;
The first Load statement loads records from the second - or rather, the second Load is piped into the first. This way you get a flag for which day the work item is created and you can use Sum(IsCreatedOn) to count the created work items and plot that against week no.
Create your master calendar this way, with the fields you want:
Load distinct Date,
Week(Date) as WeekNo,
Date(MonthStart(Date),'YYYY-MMM') as Month,
Month(Date) as Month,
Year(Date) as Year
resident Table2;
HIC
I would use three tables:
Table2 can be created through
Load WorkItemID, Date(CreatedOn+IterNo()-1) as Date
resident Table1
While IterNo() <= ClosedOn - CreatedOn + 1;
Then you can count the number of days per work item and the number of work items per date/week/month using a simple Count(distinct ...)
HIC
Hi Henric,
Thanks a lot. This is very helpful when I want to see the number of open records per week (I am using Count(distinct WorkItemID) ).
But my primary goal is to get the number of records created and closed per week. So for a particular record that was created in week w1 and closed in week w2, I only want it to appear in w1 for the "Created" expression and in w2 for the "Closed" expression.
Basically I would need something like 'Count(distinct WorkItemID) - "Count(distinct(WorkItemID of previous week" ' for the number of created records and 'Count(distinct WorkItemID) - "Count(distinct(WorkItemID of next week"' for the number of closed records. I'm sure there is a way to define this, but this is my first week into Qlikview and I'm still a bit overwhelmed by all its functions and features so any hints are very welcome.
Thank you.
Use the above data model, but load Table2 the following way
Load WorkItemID, Date,
if(Date=CreatedOn, 1, 0) as IsCreatedOn,
if(Date=ClosedOn, 1, 0) as IsClosedOn;
Load WorkItemID, ClosedOn, CreatedOn,
Date(CreatedOn+IterNo()-1) as Date
resident Table1
While IterNo() <= ClosedOn - CreatedOn + 1;
The first Load statement loads records from the second - or rather, the second Load is piped into the first. This way you get a flag for which day the work item is created and you can use Sum(IsCreatedOn) to count the created work items and plot that against week no.
Create your master calendar this way, with the fields you want:
Load distinct Date,
Week(Date) as WeekNo,
Date(MonthStart(Date),'YYYY-MMM') as Month,
Month(Date) as Month,
Year(Date) as Year
resident Table2;
HIC
Hi,
i will provide test scripting for that one,
Test:
load ID,
Name,
Createdate AS lInkDate,
close date
'Creat' AS Flag
from x Table;
concatenate(Test)
load ID,
Name,
CreatDate,
Closedate AS linkDate,
'Close' AS Flag
from x Table;
Calendar:
load linkDate,
Month,
Year,
Week
from Calemdar;
closed and create both will be link to same calendar
w1 it display w1 -clsed according to closed
w1 it display w1-created according to created.
i think it will be use full.
Awesome, thanks Henric! Now it looks like expected.
The only issue I found was that for records that are not yet closed, no entries in Table2 were created because the while loop does not run.
I believe I was able to solve this by adding the following code:
Concatenate (Table2) LOAD WorkItemID,
CreatedOn AS Date,
1 AS IsCreatedOn,
0 AS IsClosedOn
RESIDENT Table1
WHERE ISNULL("ClosedOn");
Thanks and regards.
That will solve the problem with the flags, but you will not get the dates between CreatedOn and today...
An alternative is to check for NULLs when creating Table2, e.g.:
Load WorkItemID, Date,
if(Date=CreatedOn, 1, 0) as IsCreatedOn,
if(Date=ClosedOn, 1, 0) as IsClosedOn;
Load WorkItemID, ClosedOn, CreatedOn,
Date(LowerBound+IterNo()) as Date
While IterNo() <= UpperBound - LowerBound;
Load WorkItemID, CreatedOn, ClosedOn,
if(IsNull(CreatedOn), ClosedOn, CreatedOn)-1 as LowerBound,
if(IsNull(ClosedOn), Today(), ClosedOn) as UpperBound
resident Table1;
Then you will get the dates between CreatedOn and Today() also.
HIC