Count weekdays

[#1]

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!

[CELAMBARASAN]

Hi,

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

Celambarasan

[#3]

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

[#4]

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

[jagan]

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)

Or

Just drag the Day dimension horizontally and use

Count(Day)

Hope this helps you.

Regards,

Jagan.

[perumal_41]

Hi

Try This Expression

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

Regards

Perumal

[#7]

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

Any thoughts ?

Thanks a lot!

[jagan]

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.

[CELAMBARASAN · Accepted solution]

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