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

[Anonymous]

Hello,

I think you can solve this in two steps.

1. Mark the correct Sundays or Mondays in the script. Something like this should work:

//I go for Sundays

LOAD

if((weekday(DATE)=6 and num(month(DATE))=num(month(num(DATE)+7)))

OR

(weekday(DATE)=6 and day(DATE)<7)

, 1,0)

FROM SOURCE;

2. At front end, just count or sum the flags in a month. SUM(FLAG) or AGGR(SUM(FLAG),MONTH)

BR

Serhan

[#3]

Hi

I don't need the count of sundays, i need the count of emails sent in that campaign or in that month.

[Anonymous]

Hello,

In your original post, you are asking for campaign counts. Try to be clearer next time and remember the principle "please would be nice with a sugar on top".

Plus, if you just do the same thing in CAMPAIGN table you can achieve the emails as well with a proper set analysis.

BR

Serhan

[brunobertels]

Hi

So chek this APP

it gives you :

A month_period using the requiered week per month

a count of email sent per month

A KPI with actual month and previous month

mesures and dimension are to renamed as needed

Regards

[#6]

hi Bruno Bertels,

Now, Month is in the form "MMM"  How can i add or display in the format "MMM-YY"

Pramod

[brunobertels]

Hi

Add this in your script :

date(weekend(Calendrier),'MMM-YY') as Month_Year,

regards

Bruno

[#8]

For this script how can i add

month(weekend(canonicaldate)) as MonthPeriod?

I am currently using,

Dimension : MonthPeriod

Measure:  Count({$<DateType = {invitation},MonthPeriod= {"<$(=max(MonthPeriod))"} >}invitation_id)

Not working for this script : date(weekend(CanonicalDate),'MMM-YY') as MonthPeriod

and how can i get the number of days in monthperiod

for example: 1. From Nov-30 to Dec-27

2. Oct-26 to Nov-29

Pramod

[brunobertels]

Hi

First may be change the dimension of MonthPeriod with this script :

month(weekend(CanonicalDate))& year(weekend(CanonicalDate)) as MonthPeriod,

test your mesure

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

may be change this : add single quote around invitation : 'invitation'

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

then

To get the number of days for each month period use this mesure :

aggr(count(CanonicalDate),MonthPeriod)

will return the number of days for each month period :

Bruno

[#10]

This will give the count 4 as this MonthPeriod starts from 30th and today is 3rd so count is 4 that's correct.

aggr(count(CanonicalDate),MonthPeriod)

but my requirement is from 30-Nov to 29-Dec  =  28

How can i get this?

[brunobertels]

Hi

It sounds like 27 december and not 29 dec ? Right ?

in my test app it works :

then if I well understood , your CanonicalDate is made with a loop with first date in you dataset and lastdate or today()  or Maxdate() from your data ?

So the mesure give 4 rather than 28 because we are the third of december .

Is that how it works ?

In that case i don't see how to manage this because aggr function are not allowed in script side.

sorry

Bruno