Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Week Start

Hi all,

As i have a two campaigns one start on Monday and ends on Sunday.

and one starts on Thursday and ends on Wednesday.

Now i am calculating monthly data from first Monday to last Sunday of the month using below load Script

Date(MonthStart(weekend(CanonicalDate)),'MMM-YY') as MonthPeriod1

Expression:

count({$<MonthPeriod1= {"$(=Date(MonthStart(weekend(Max(CanonicalDate))),'MMM-YY'))"},

  DateType= {'invitation'},batch_meta_data_id = {'35'}>} invitation_id)

Now i have to calculate Monthly data for second campaign which starts on Thursday so i need Data from first Thursday to last Wednesday of the month. How can i achieve this without affecting the first values.

Regards,

Pramod

12 Replies
Not applicable
Author

WEEKEND lets you define the first day of the week, so:

WEEKEND(MONTHSTART(CanonicalDate),0,4)

will give you the first Thursday of the month

WEEKSTART(MONTHEND(CanonicalDate),0,2)

will give you the last Wednesday

Not applicable
Author

Hi Thanks for the reply.

I used expression like below but it's not working

count({$<CanonicalDate= {">=$(Date(WEEKEND(MONTHSTART(CanonicalDate),0,4)))<=$(Date(WEEKSTART(MONTHEND(CanonicalDate),0,2)))"},

  DateType= {'invitation'},batch_meta_data_id = {'24'}>} invitation_id)

Not applicable
Author

Make sure the date formatting matches e.g. try Date(WEEKEND(MONTHSTART(CanonicalDate),0,4),'YYYY-MM-DD')

assuming CanonicalDate is in that format

Not applicable
Author

I have Canonical date in format MM-DD-YYYY,

so i am using like below:

count({$<CanonicalDate= {">=$(Date(WEEKEND(MONTHSTART(CanonicalDate),0,4),'MM-DD-YYYY'))<=$(Date(WEEKSTART(MONTHEND(CanonicalDate),0,2),,'MM-DD-YYYY'))"},

  DateType= {'invitation'},batch_meta_data_id = {'24'}>} invitation_id)

Instead of getting the value of present month it's fetching the whole data of that batch.

Not applicable
Author

try:

count({$<CanonicalDate= {">=$(=Date(WEEKEND(MONTHSTART(CanonicalDate),0,4),'MM-DD-YYYY'))<=$(=Date(WEEKSTART(MONTHEND(CanonicalDate),0,2),,'MM-DD-YYYY'))"},

  DateType= {'invitation'},batch_meta_data_id = {'24'}>} invitation_id)

Note the additional = within the $ notation

Not applicable
Author

You are checking CanonicalDate against the month start and month end of CanonicalDate - it will always be within those bounds

Not applicable
Author

It's not working.... returning zero value

count({$<CanonicalDate= {">=$(=Date(WEEKEND(MONTHSTART(CanonicalDate),0,4),'MM-DD-YYYY'))<=$(=Date(WEEKSTART(MONTHEND(CanonicalDate),0,2),'MM-DD-YYYY'))"},

  DateType= {'invitation'},batch_meta_data_id = {'24'}>} invitation_id)

Not applicable
Author

assuming you want the most recent months data (as your first set analysis)

count({$<CanonicalDate= {">=$(=Date(WEEKEND(MONTHSTART(MAX(CanonicalDate)),0,4),'MM-DD-YYYY'))<=$(=Date(WEEKSTART(MONTHEND(MAX(CanonicalDate)),0,2),'MM-DD-YYYY'))"},

  DateType= {'invitation'},batch_meta_data_id = {'24'}>} invitation_id)

added MAX() to CanonicalDate

Not applicable
Author

Sorry dint get you?

please elaborate?