Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Find Number of days

Hi all,

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.


I achieved this by using script:

date(weekend(CanonicalDate),'MMM-YY') as MonthPeriod


MonthPeriod will give the require counts as above dates.


1. now i need to calculate the Number of days in MonthPeriod. How can i do this?

Ex: Number of days in Nov : 35

2. Also, If i will be in the middle of the MonthPeriod, then the number of days passed from start of month period to till that date.

Ex: if i will be on Nov 15 th then number of days passed should be 21.


How can i achieve this?



Thanks,

Kumar

Tags (1)
4 Replies
arasantorule
Contributor III

Re: Find Number of days

Hi,

You will need to create an additional Calendar to achieve this easily.

Create a calendar say First Day of the Month is Last Monday of the previous Month and Last day on the month is last Sunday of the month.

The calendar will look like below.

Month 1 - Jan - (29Dec2014 - 25Jan2015)

Month 2 - Feb - (26Jan2015 - 28Feb2015) and So on


Once the calendar is created and associated to your data model you will get your results as expected


Hope this helps.


Thanks

Not applicable

Re: Find Number of days

I think this will not works, because we are not taking all last monday of previous month as startday of the present month,

because we will take only in the situation where campaign/week starts in last month and ends in present month.

In the above scenario only we will take last monday of previous month as start day of present month.

Not applicable

Re: Find Number of days

jontydkpi

Hi jonathan,

i found your answer in below discussion.

Count number of Monday's, Tuesday's ... from a StartDate to selected Year/Month

can you please provide the .qvf file as i don't have qlikview.

And i need counts of tuesday for above scenario.

Means number of tuesday in November month as i take month as explained above and also if i am in middle of the month number of tuesday passed from start of the month.

Can you please help with it.

Thanks,

Not applicable

Re: Find Number of days

Any suggestions/help over this?

Community Browser