Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Distributing Days across Months

Hi,

I have two dates

start date: 01/05/2017    

EndDate: 03/06/2017

I want to distribute the number of days between the days to the months.

start date: 01/05/2017     jan   feb   mar   apr  may  jun   jul  aug sep oct   nov   dec

EndDate: 03/06/2017        0      0     0       0      31    3      0     0    0    0      0       0

How can I achieve this?

25 Replies
Highlighted
Master III
Master III

See Attachment

Highlighted
Contributor III
Contributor III

what is the variable that you have taken?

Highlighted
Contributor III
Contributor III

i have a Common date from which I will take the months across which I will distribute the data

so with this expression

Sum(If([common date]>=$(=Min(StartDate)) and [common date]<= $(=Max(EndDate)),1,0))

And also I will have 10k such values for start date and end date for each of the combination I would want to distribute the number.

But with the above expression I am not getting anything in the chart.


regards,

Highlighted
Contributor III
Contributor III

I had tried nested if but seems to have some error

//IF(LinkMonth>=Month(ADVOCACY_FROM),

//IF(Month(ADVOCACY_TO)>=LinkMonth AND Year(ADVOCACY_TO)=LinkYear,

//IF(Month(ADVOCACY_FROM)=MONTH(ADVOCACY_TO) AND Year(ADVOCACY_FROM)=Year(ADVOCACY_TO) and day(ADVOCACY_TO)<>day(ADVOCACY_FROM),

//Interval(((ADVOCACY_TO)-ADVOCACY_FROM),'DD')+1,

//IF(Month(ADVOCACY_FROM)=MONTH(ADVOCACY_TO) AND Year(ADVOCACY_FROM)=Year(ADVOCACY_TO) and day(ADVOCACY_TO)=day(ADVOCACY_FROM),1,

//IF(LinkMonth=Month(ADVOCACY_FROM) AND Year(ADVOCACY_FROM)=LinkYear,

//Interval((MonthEnd(ADVOCACY_FROM)-ADVOCACY_FROM),'DD'),

//IF(LinkMonth=Month(ADVOCACY_TO) AND Year(ADVOCACY_TO)=LinkYear,

//Interval((MonthStart(ADVOCACY_TO)-ADVOCACY_TO),'DD'),

//IF(Date(MonthStart(ADVOCACY_FROM),'MMM-YYYY')<Date(MonthStart(LinkDate),'MMM-YYYY')

//AND Date(MonthStart(LinkDate),'MMM-YYYY')<Date(MonthStart(ADVOCACY_TO),'MMM-YYYY'),

//Floor(MonthEnd(LinkDate)) - Floor(MonthStart(LinkDate)) + 1))

//

//)))))

Highlighted
Master III
Master III

See new Attachment.

View solution in original post

Highlighted
Master
Master

Hi Rahul,

Use a calendar in your script. If you don't have one try something like:

Let vFirstDay =num(Date('01/01/2017'));

Let vLastDay = num(Date('31/12/2017'));

Calendar:

LOAD 

    Month(Date) As Month,

    Date;

Load Date($(vFirstDay) + RecNo() -1 ) AS Date

AutoGenerate $(vLastDay) - $(vFirstDay) +1;

Now this expression in a straight table should work for you:

Count({$<Date = {">=$(vStartDate)<=$(vEndDate)"}>}Date)

where  vStartDate and vEndDate are your own variables

Month Count({$<Date = {">=01/05/2017<=03/06/2017 "}>}Date)
34
May31
Jun3

or you can add an extra expression Count(Date) and in the Presentation tab set that new column to hidden to get:

Month Count({$<Date = {">=01/05/2017<=03/06/2017 "}>}Date)
34
Jan0
Feb0
Mar0
Apr0
May31
Jun3
Jul0
Aug0
Sep0
Oct0
Nov0
Dec0

Cheers

Andrew

Highlighted
Contributor III
Contributor III

Hi Andrew,

thanks for the reply.

But my first date and last date comes from a database table which has million of first date and last date

So I need some expression in the front end which could use the database fields and give the output.

Regards,

Highlighted
Contributor III
Contributor III

Capture.JPG

hi this how my output is coming

Highlighted
Master III
Master III

Try this Expression

Count(DISTINCT If(Date >=StartDate and Date <= EndDate,Date))

Highlighted
Contributor III
Contributor III

No Antonio,

It gives just 1 on the months where the months of endate and start date have same month rest all are blanks