topic Count weekdays in QlikView

Count weekdays

Tue, 06 Mar 2012 11:34:16 GMT

Hi all.

i want to create a table that lists for each weekday within a quarter how often this weekday occurs.

I want to start with the year 2008 and i want to doch this until 2015.

Example for 2008 (i did not check if the numbers make sense):

Quarter	# of Mondays	# of tuesdays	# of Wednesdays	# of thursdays	# of Fridays	# of Saturdays	# of Sundays
Q1-2008	20	20	20	20	20	21	21
Q2-2008	19	19	19	19	19	18	18
Q3-2008	21	21	21	21	21	20	20
Q4-2008	20	20	20	20	20	20	20

Thanks a lot!

Count weekdays

CELAMBARASAN — Tue, 06 Mar 2012 12:00:35 GMT

Hi,

You mean number of mondays occur in that quarter like that?

Celambarasan

Count weekdays

Tue, 06 Mar 2012 12:08:26 GMT

Please create a standard calendar table

with YearQuarter and Weekday in it

All you need to Pivot on these values and do the count

Regards

Kulbir

Re: Count weekdays

Tue, 06 Mar 2012 12:14:01 GMT

Yes, exactly. I want to know f.e. the number of Mondays in a quarter..

Count weekdays

jagan — Tue, 06 Mar 2012 12:18:17 GMT

Hi,

In Load script, get the Day as separate column

LOAD

WeekDay(Day) AS Day,

FROM DataSource;

Now in straight table

Use Quarter as dimension

And use expressions like

# of Mondays =Count({<Day={'Mon'}>} Day)

# of Tuesdays =Count({<Day={'Tue'}>} Day)

# of Sundays =Count({<Day={'Sun'}>} Day)

Just drag the Day dimension horizontally and use

Count(Day)

Hope this helps you.

Regards,

Jagan.

Count weekdays

perumal_41 — Tue, 06 Mar 2012 12:19:57 GMT

Try This Expression

=Count({<Datefiled={'>=$(=min(DateFiled))<=$(=max(DateFiled))'}>}weekday(Datefiled))

Regards

Perumal

Re: Count weekdays

Tue, 06 Mar 2012 12:23:01 GMT

Thanks for all the ideas and replies, but: i want to do this in the LOAD script only.

Any thoughts ?

Thanks a lot!

Count weekdays

jagan — Tue, 06 Mar 2012 12:26:41 GMT

Hi,

Try this

TempTable:

LOAD

WeekDay(Day) AS Day,

FROM DataSource;

LOAD

Quarter,

Day,

Count(Day) AS Count

RESIDENT TempTable

Group By Quarter,Day;

Regards,

Jagan.

Re: Count weekdays

CELAMBARASAN — Tue, 06 Mar 2012 12:31:10 GMT

Hi,

Do you have a Master calendar?

If not create like this.

Calendar:

Load

Date(Date#('1/1/2008','D/M/YYYY')+RowNo()-1) as Date,

QuarterName(Date#('1/1/2008','D/M/YYYY')+RowNo()-1) as Quarter,

WeekDay(Date#('1/1/2008','D/M/YYYY')+RowNo()-1) as WeekDay

AutoGenerate

Interval(Date#('31/12/2015','D/M/YYYY')-Date#('1/1/2008','D/M/YYYY'),'D');

NumberOfDays:

Load

Quarter,

Count('*') as Monday

Resident Calendar where WeekDay=0

Group By Quarter;

Left join

Load

Quarter,

Count('*') as Tuesday

Resident Calendar where WeekDay=1

Group By Quarter;

Left join

Load

Quarter,

Count('*') as Wedesday

Resident Calendar where WeekDay=2

Group By Quarter;

Left join

Load

Quarter,

Count('*') as Thursday

Resident Calendar where WeekDay=3

Group By Quarter;

Left join

Load

Quarter,

Count('*') as Friday

Resident Calendar where WeekDay=4

Group By Quarter;

Left join

Load

Quarter,

Count('*') as Saturday

Resident Calendar where WeekDay=5

Group By Quarter;

Left join

Load

Quarter,

Count('*') as Sunday

Resident Calendar where WeekDay=6

Group By Quarter;

Celambarasan