Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

Counting number of Mondays for running month

Avatar

Avatar
Validate 1
Level 1
antti_nylund
Level 1

Likes

0 likes

Total Posts

5 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile

Avatar
Validate 1
Level 1
antti_nylund
Level 1

Likes

0 likes

Total Posts

5 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile
antti_nylund
Level 1

04-02-2019

I'm trying to get the number of Mondays in this month (February).

Today is the first Monday of Feb so any way I try I get value 1.

The goal would be to get value 4 which is the number of Mondays.

Any ideas?

Replies

Avatar

Avatar
Coach
Employee
Gigazelle
Employee

Likes

470 likes

Total Posts

1,947 posts

Correct Reply

739 solutions
Top badges earned
Coach
Contributor 2
Validate 1
Ignite 80
Ignite 70
View profile

Avatar
Coach
Employee
Gigazelle
Employee

Likes

470 likes

Total Posts

1,947 posts

Correct Reply

739 solutions
Top badges earned
Coach
Contributor 2
Validate 1
Ignite 80
Ignite 70
View profile
Gigazelle
Employee

07-02-2019

I'm not sure I understand you question. Can you clarify what you're looking for and how you intend to use it in reporting?

Avatar

Avatar
Validate 1
Level 1
antti_nylund
Level 1

Likes

0 likes

Total Posts

5 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile

Avatar
Validate 1
Level 1
antti_nylund
Level 1

Likes

0 likes

Total Posts

5 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile
antti_nylund
Level 1

14-02-2019

I would like to build a daily target. I have monthly target of 100%. Data indicates that in average Monday, Tuesday and Thursday deliver 25% of Monthly target each and the rest 25% is divided with the rest of the days. I calculated the average from 3 months and made sure that each weekday was present same amount of times.

Since amount of weekdays is changing by the month I cannot use static number like 4. If I use COUNT IF day=Monday function in early Feb it would give me 0 or 1 depending if there has already been 1 Monday.

So if I want to calculate target for all Mondays in Feb (before Feb is completed):

100%*0.25 / COUNT Mondays (in Feb this translates to 0.0625) but in April the number would be 0.05 since there are 5 Mondays in March.

Even though Adobe prints all dates from Feb it does not seem to be able to calculate how many Mondays there is going to be. Counting weekdays works fine if I calculate the target for January, but that kinda defeats the purpose.

I have now resorted adding the number as a static value but if you guys have any suggestion I welcome them gladly.

Avatar

Avatar
Validate 1
Level 1
antti_nylund
Level 1

Likes

0 likes

Total Posts

5 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile

Avatar
Validate 1
Level 1
antti_nylund
Level 1

Likes

0 likes

Total Posts

5 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile
antti_nylund
Level 1

14-03-2019

So currently:

Yearly target: 100

Monthly multiplier (towards yearly goal): 0.1

Target for this month: 10

Number of each weekday in this month: 4 (February is easy)

Multipliers for each weekday:

Mon: 0.25

Tue: 0.25

Wed: 0.09

Thu:0.25

Fri: 0.04

Sat:0.06

Sun:0.06

Monthly and weekday multiplier were calculated based on last year performance

So my target for

1st of Feb (Friday) would be

100*0.1*(0.04/4) = 0.1

2nd of Feb (Saturday)

100*0.1*(0.06/4) = 0.15

3rd of Feb (Sunday)

100*0.1*(0.06/4) = 0.15

4th of Feb (Monday)

100*0.1*(0.25/4) = 0.625

5th of Feb (Tuesday)

100*0.1*(0.25/4) = 0.625

6th of Feb (Wednesday)

100*0.1*(0.09/4) = 0.225

7th of Feb (Thursday)

100*0.1*(0.25/4) = 0.625

So the problem was that I cannot get the number of each weekday in a month dynamically.

COUNT only returns number of Mondays from the past. for example if date is 8th of Feb COUNT returns 1

Any comments?

Avatar

Avatar
Coach
MVP
ursboller
MVP

Likes

522 likes

Total Posts

1,020 posts

Correct Reply

256 solutions
Top badges earned
Coach
Contributor
Bedrock
Seeker
Springboard
View profile

Avatar
Coach
MVP
ursboller
MVP

Likes

522 likes

Total Posts

1,020 posts

Correct Reply

256 solutions
Top badges earned
Coach
Contributor
Bedrock
Seeker
Springboard
View profile
ursboller
MVP

14-03-2019

you can use "approximate count distinct" with a weekday segment to get the number of a certain werkday in a specific month. but this only works for past data since you need to have at least one hit on each weekday to coont agsinst...

I would do this in excel or any other tool outside of analytics...

If you want to use workspace (and calculated metrics) you might try to upload a metric for each day using data source. basically you could upload a metric value for each day which indicates the number of werkdays the month have... (would be 4 for each day in february...)

the drawback is, that you can't upload data for the future, you might need to upload every day.... (hope I'm not wrong with this statement...)