topic Re: Weekly volume derived from 2 date fields in QlikView

Weekly volume derived from 2 date fields

Wed, 03 Oct 2012 12:11:24 GMT

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

Re: Weekly volume derived from 2 date fields

Anonymous — Thu, 04 Oct 2012 02:22:21 GMT

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.

Re: Weekly volume derived from 2 date fields

rwunderlich — Thu, 04 Oct 2012 03:16:31 GMT

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

Re: Weekly volume derived from 2 date fields

Wed, 10 Oct 2012 15:46:24 GMT

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;