Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Master III
Master III

Re: Distributing Days across Months

See Attachment

Highlighted
Contributor III
Contributor III

Re: Distributing Days across Months

what is the variable that you have taken?

Highlighted
Contributor III
Contributor III

Re: Distributing Days across Months

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

Re: Distributing Days across Months

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

Re: Distributing Days across Months

See new Attachment.

View solution in original post

Highlighted
Master
Master

Re: Distributing Days across Months

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

Re: Distributing Days across Months

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

Re: Distributing Days across Months

Capture.JPG

hi this how my output is coming

Highlighted
Master III
Master III

Re: Distributing Days across Months

Try this Expression

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

Highlighted
Contributor III
Contributor III

Re: Distributing Days across Months

No Antonio,

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