Month start and month end

[#1]

Hi all,

jagan‌

I have campaigns which starts on monday and ends on sunday.now i need the counts based on these campaigns and also month wise.

for example:  for month wise counts : in October last week the campaign starts on 26/10/2015 and ends on 01/11/2015.

Now for the October month count i need to exclude this campaigns data and include it to november month count.

My actual concept is if campaign starts in the last month and ends in present month, then My month start or counts should be taken from that campaign start date of the last month, and the month end or counts till the last sunday of the current month.

Example: 1)  October month count: from sep 28th  to   Oct 25th.

               2)  November month count : from oct 26th  to Nov 29th.

Help on this how can i achieve this, through load script or set analysis.

Thanks,

Kumar

[#2]

Hi Bruno,

I am using below expression for measure to get the previous count, but count is mismatch

Count({$<DateType = {'invitation'},MonthPeriod= {">=$(=max(MonthPeriod-1))<$(=max(MonthPeriod))"} >}invitation_id)

Please check and help on it

[#3]

I think what you tell is correct.

But i need bot the counts means no.of days completed i.e 4 and also no. of days in month period i.e 28.

how can i get the both counts bruno?

[brunobertels]

Hi

For previous mounth count it should be :

Count({$<DateType = {'invitation'},MonthPeriod= {"=$(=max(MonthPeriod-1))"} >}invitation_id)

bruno

[brunobertels]

Hi

Sorry I don't see a solution for instance to get the total number of days depending of your particular calculation for month period on current month.

Try to make a new post with this particular quesiton in the forum.

Add your app so that it will be easier for community to help

Bruno

[#6]

Hi sangram,

If i use above expression i am getting the count of Monthstart to MonthEnd.

But the count i needed is As per the 'MonthPeriod'

I am getting count mismatch when i compare with DB, finding difficulty to find where i am missing

[#7]

Hi Bruno,

Can you have any other suggestions to achieve this?

[brunobertels]

Hi

Sorry , unfortunately i don't see how to achieve this.

Bruno

[#9]

hi bruno,

how if i create a table with two columns

MonthPeriod, No.of days in month period

Nov                35

Dec                 28

like above hardcoding for some two years then it will be good right?

Can you help how can i create a table like above?

Thanks,

Pramod

[brunobertels]

Hi

Add an additional section in script with this code

	LET vCalendarStart = Date#('01/01/2015');  //enter the first date of your data
	LET vCalendarEnd = YearEnd(today());  // Or this LET vCalendarEnd = Date#('01/01/2017') to have 2 years of dates
	LET vCalendarLength = $(#vCalendarEnd) - $(#vCalendarStart) + 1;
	Calendrier1:
	LOAD
		Date($(#vCalendarStart) + RecNo()-1) AS "Date"
		AutoGenerate $(#vCalendarLength);
	Calendrier2:
	LOAD
	Date,
	Year(Date) AS Year,
	Num(Month(Date), '00') AS Month,
	month(weekend(Date))& year(weekend(Date)) as MonthPeriod,
	Num(Day(Date), '00') AS Day,
	Num(Week(Date), '00') AS Week,
	weekday(Date) as Nom_jour, // numero de jour de 0 à 7 = num(weekday(Date),'00')
	date(weekend(Date),'MM-YY') as MoisPeriode

	resident Calendrier1;
	drop table Calendrier1;

[#11]

Can you explain which set expression can i use

1. to get the number of days in monthperiod.

2. if date is dec-11-2015 the number of days completed till that date in monthperiod or number of days passed from today in monthperiod.

3. # of tuesdays in a monthperiod

4. # of tuesday passed from today()