topic Re: Distributing Days across Months in QlikView

Distributing Days across Months

rahulsingh12 — Fri, 23 Jun 2017 00:39:33 GMT

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?

Re: Distributing Days across Months

sunny_talwar — Fri, 23 Jun 2017 01:12:14 GMT

You want this in the script or front end?

Re: Distributing Days across Months

rahulsingh12 — Fri, 23 Jun 2017 01:15:42 GMT

In front end if possible.

Once the data is matches then might move it to the script level.

Regards,

Rahul

Re: Distributing Days across Months

rahulsingh12 — Fri, 23 Jun 2017 01:19:01 GMT

I wanted it in front end.

Regards,

Re: Distributing Days across Months

rahulsingh12 — Fri, 23 Jun 2017 04:05:28 GMT

yes

Regards,

Re: Distributing Days across Months

sunny_talwar — Fri, 23 Jun 2017 08:48:53 GMT

Would you be able to share a sample to show your data model so that I can propose the best possible way to do this?

Re: Distributing Days across Months

rahulsingh12 — Fri, 23 Jun 2017 20:38:24 GMT

Hi i wont be able to share the sample data as i am on a client visit.

Consider the above two as my data with only change that the months across which the days is to be distributed is coming from the two dates only.

Regards,

Re: Distributing Days across Months

antoniotiman — Sat, 24 Jun 2017 05:31:12 GMT

Hi Raul,

StartDate = 10/04/2017

EndDate = 03/06/2017

See Attachment.

Regards,

Antonio

Re: Distributing Days across Months

rahulsingh12 — Sat, 24 Jun 2017 06:22:32 GMT

can you share the code you have written as i don't see any attachment.

regards,

Re: Distributing Days across Months

rahulsingh12 — Sat, 24 Jun 2017 06:26:43 GMT

got it.

thanks

Re: Distributing Days across Months

antoniotiman — Sat, 24 Jun 2017 06:31:52 GMT

See Attachment

Re: Distributing Days across Months

rahulsingh12 — Sat, 24 Jun 2017 06:45:28 GMT

what is the variable that you have taken?

Re: Distributing Days across Months

rahulsingh12 — Sat, 24 Jun 2017 06:48:53 GMT

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,

Re: Distributing Days across Months

rahulsingh12 — Sat, 24 Jun 2017 06:53:22 GMT

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))

//)))))

Re: Distributing Days across Months

antoniotiman — Sat, 24 Jun 2017 06:59:57 GMT

See new Attachment.

Re: Distributing Days across Months

effinty2112 — Sat, 24 Jun 2017 07:54:17 GMT

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
May	31
Jun	3

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
Jan	0
Feb	0
Mar	0
Apr	0
May	31
Jun	3
Jul	0
Aug	0
Sep	0
Oct	0
Nov	0
Dec	0

Cheers

Andrew

Re: Distributing Days across Months

rahulsingh12 — Sat, 24 Jun 2017 08:14:56 GMT

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,

Re: Distributing Days across Months

rahulsingh12 — Sat, 24 Jun 2017 08:17:47 GMT

hi this how my output is coming

Re: Distributing Days across Months

antoniotiman — Sat, 24 Jun 2017 08:22:04 GMT

Try this Expression

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

Re: Distributing Days across Months

rahulsingh12 — Sat, 24 Jun 2017 08:34:03 GMT

No Antonio,

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