topic Count number of Monday's, Tuesday's ... from a StartDate to selected Year/Month in QlikView

Count number of Monday's, Tuesday's ... from a StartDate to selected Year/Month

Anonymous — Mon, 04 Oct 2010 10:28:34 GMT

I'm looking for a way of calculating the number of Monday's, Tuesday's ... etc from a StartDate until a certain selected year/month.

Does anyone have idea's how to do something like this??

e.g.

Callnr, StartDate

1, 1-9-2009
2, 4-9-2009
3,19-8-2010

Selected YEAR / MONTH = 2010 / September

In this case I need to calculate the number of days between the StartDate and the Year/Month selections. This is not that hard ...

But what I really need is how many of each Weekday is between the StartDate and the Year/Month selection.

Anyone got suggestions??

thx

Count number of Monday's, Tuesday's ... from a StartDate to selected Year/Month

syed_muzammil — Mon, 04 Oct 2010 10:47:40 GMT

Hi,

You can use the function weekday(Date) during load. This will get you the weekday numer and inthe front end youcan give count(weekday field) where weekday feild = 0 (for Monday)

Regards,

Syed.

Count number of Monday's, Tuesday's ... from a StartDate to selected Year/Month

Anonymous — Mon, 04 Oct 2010 10:57:28 GMT

At the backend I'm already loading the weekday ... but still got some issues because this is in a separate table which is not connected to the Call table containing the startdate

Think I should do some tricks with expressions on the frontend to solve this ... which will do something with the summing of the weekday ...

Thx

Count number of Monday's, Tuesday's ... from a StartDate to selected Year/Month

jonathandienst — Mon, 04 Oct 2010 11:04:30 GMT

This is a sample that does what I think you want.

Jonathan

Count number of Monday's, Tuesday's ... from a StartDate to selected Year/Month

johnw — Tue, 05 Oct 2010 01:17:11 GMT

Here's a script solution. It builds two new tables. One matches the StartDate to every date between then and today, and has a day of week field in it. One matches an AsOfDate to every date between then and a year ago (or as early as your data goes). The chart is then a simple pivot table:

dimension 1 (left) = Callnr
dimension 2 (left) = StartDate
dimension 3 (top) = DayOfWeek
expression = count(Date)

An amusing way to see it working is to select an AsOfDate, and then use the up and down arrow keys move the selected date forward or backward, and watch it increment and decrement the column for that weekday.

Count number of Monday's, Tuesday's ... from a StartDate to selected Year/Month

Anonymous — Tue, 05 Oct 2010 09:04:11 GMT

This is exactly what I was looking for ... thx 😉

I'm not really into the while looping yet ... so I'm learning each day