Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
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