Weekly volume derived from 2 date fields

[#1]

Hi All,

I'm a Qlikview newbie and have spent the morning trying to achieve something that I find fairly straightforward in MS Access. A bit of a learning exercise if you like. Here's what I'm trying to do...

I have a table of assets, each with a DateAcquired and DateReleased as below (DateReleased is null where we still have the asset)...

AssetID    DateAcquired    DateReleased

12345        01/09/2012      25/09/2012 

12346        10/09/2012      -

12347        12/09/2012      01/10/2012

12348        13/09/2012      -

From this I need to derive a weekly volume of assets held based on the two date fields.

Now in MS Access I would first create a table of week numbers with start and end dates like this...

Week    WeekStart       WeekEnd

37         10/09/2012      16/09/2012

38         17/09/2012      23/09/2012

39         24/09/2012      30/09/2012

40         01/10/2012      07/10/2012

...then create a query with both the asset table and week numbers table (but without a join). I can then get a count of assets grouped by week number by adding the following criteria: DateAcquired<=WeekStart, DateReleased>WeekEnd OR IS NULL.

So far in Qlikview I've loaded my asset table and created a calendar table like the one above. I've tried using set analysis with similar critera to my Access query but I suspect I'm barking up the wrong tree with that. Basically I've reached the limits of my understanding and hope that someone out there can set me on the right track.

Many Thanks,

John

[Anonymous · Accepted solution]

Hi John,

You might want to look into IntervalMatch() in the QlikView help or on the forums.

If you list all dates and weeks in your calendar you should be able to synch these numbers within the ranges of DateAcquired and DateReleased for all your AssetIDs.

[rwunderlich]

Echoing what Johannes suggested, there is similar example in the QV Cookbook. Can't recall the exact name, but I think it has "intervalmatch" in the description. It  uses intervalmatch to count days for a piece of rental equipment. You could modify it to match weeks instead. Or match on date and count on distinct week to get a count.

-Rob

[#4]

Guys, many thanks for your help. I've posted my code below should it be of benefit to anyone else. It works fine but I suspect my approach to arriving at a count by week is a little long winded. Maybe there's a smarter way of using IntervalMatch?

Cheers,

John.

Let varMinDate = Num('01/01/2012');

Let varMaxDate = Num(Weekstart(Today())-1);

Calendar:
LOAD
               $(varMinDate) + Iterno()-1 As Num,
               Date($(varMinDate) + IterNo() - 1) as CalendarDate
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
              
SET NullValue = '31/12/2199'; //replace the null ReleaseDates with this date

NULLASVALUE ReleaseDate; //switch this on

Assets:
LOAD Num(AssetID) as AssetID,
    Date(DateAcquired) AS DateAcquired,
    Date(ReleaseDate) AS ReleaseDate,
    1 as AssetCount;
SQL SELECT AssetID,
    DateAcquired,
    ReleaseDate
FROM tblAsset
WHERE AssetGroupID = 1
AND AssetTypeID IN(3)
AND Deleted = False;

NULLASNULL; //Switch NullAsValue off

ApplyIntervalMatch:
IntervalMatch (CalendarDate) LEFT JOIN LOAD DISTINCT DateAcquired, ReleaseDate Resident Assets;
LEFT JOIN (Calendar) LOAD * Resident Assets;

DROP Table Calendar;

//Get count of assets by week
AssetCountDaily:
Load CalendarDate,
Sum(AssetCount) as AssetCount
Resident Assets
Group By CalendarDate;

AssetCountWeekly:
Load Distinct Week(CalendarDate) as WeekNum,
Min(AssetCount) as AssetCount // I only want assets that are with us for the entire week
Resident AssetCountDaily
Group By Week(CalendarDate);

DROP Tables Assets, AssetCountDaily;