11 Replies Latest reply: Jan 28, 2013 4:43 AM by Kumar Natarajan

# No Of Days For Selected Period?

Hi All,

I want to show no.of days for selected time period.

Suppose if i selected Apr = 30, May=31, Just like if i selected Quarter 1 Apr+May+Jun(30+31+30)=91, if i selected Q1+Q2= 183 days,

if i selected combination that means if i selected june n july 61.... i want it in dynamically, in expression, for month i am getting the values, but when i am taking quarter its showing null,

Help me regarding this

• ###### Re: No Of Days For Selected Period?

use networkdays function which takes two dates from date and to date.

and gives the exact noofdays .and if you want exclude hlidays then also you can

see the qlikview help for networkdays networkdays function.

hope this helps

• ###### Re: No Of Days For Selected Period?

Hi LS,

I tried that function but i didnt get what i expected, i want to multiply that resultant days to my amount, it would be in dynamically,

i.e Amount*(noofdays(selected period)/NoofDaysInYear) is my result

• ###### Re: No Of Days For Selected Period?

Hi,

Count([Day (#)])

[Day (#)] as your day field

• ###### Re: No Of Days For Selected Period?

Hi Kumar,

Its giving total days upto selected date, but i want only days of that month, quarter , for suppose in year 2011, if i selected apr it will show 30, may 31, if i selected both 61, if i selected q1 then 91 like this

• ###### Re: No Of Days For Selected Period?

Hi,

Check the attached file, It will return NoOfDays based on your selection. If i understand you correctly else explain me briefly

• ###### Re: No Of Days For Selected Period?

Suppose my report is 2008 to 2012 , if i selected 2012 Apr its giving total days from 2008 to untill 2012 Apr, but i want to display 30 only, I hope i explained u clearly, if i selected Q1 i want to show 91, Q2=92, Q1+Q2=183....

like this

• ###### Re: No Of Days For Selected Period?

Hi ,

First Create Calendar,

let EndDate=yearend(today());

let StartDate=YearStart(Makedate(\$(Year_Start),1,1));

let caldays=(EndDate-StartDate)+1;

//Let vToday=Chr(39)&'20/12/2012'&Chr(39);

let vToday='Date(today())';

TempCalendar:

date(if(Recno()=1,MakeDate(\$(Year_Start),1,1),date(peek("PeriodDate")+1)),'DD/MM/YYYY') as PeriodDate

autogenerate(caldays);

Calendar_Min:

Load Date(PeriodDate) as Shipment_Date_Master_Min Resident TempCalendar;

Calendar_Max:

Load Date(PeriodDate) as Shipment_Date_Master_Max Resident TempCalendar;

Map_Qtr:

Qtr,QtrMap

Q1,Q4

Q2,Q1

Q3,Q2

Q4,Q3

];

Calendar:

Div(PeriodDate-YearStart(PeriodDate)+WeekDay(YearStart(PeriodDate))+7,7) as Week,

Month(PeriodDate) AS Month,

if(PeriodDate>=(\$(vToday)-365) and PeriodDate<=\$(vToday),Month(PeriodDate)&'-'&right(num(year(PeriodDate)),2)) as Default_12Month,

if(PeriodDate>=(\$(vToday)-90) and PeriodDate<=\$(vToday),Week(PeriodDate)&'-'&right(num(year(PeriodDate)),2)) as Default_DBPrd,

ApplyMap('Map_Qtr','Q' & ceil(Month(PeriodDate)/3),0) AS Quarter,

Left(yearname (PeriodDate, 0, 4 ),4)&'-'&right(yearname (PeriodDate, 0, 4 ),2) as Year,

//    Year(PeriodDate) AS Year,

Div(PeriodDate-YearStart(PeriodDate)+WeekDay(YearStart(PeriodDate))+7,7)&'-'&Right(Year(PeriodDate),2) as WeekYear,

Month(PeriodDate)&'-'&Right(Year(PeriodDate),2) as MonthYear,

ApplyMap('Map_Qtr','Q' & ceil(Month(PeriodDate)/3),0)&'-'&Right(Year(PeriodDate),2) as QtrYear,

day(PeriodDate) AS Day,

weekday(PeriodDate) AS Weekday,

DayNumberOfQuarter(PeriodDate) as DayQtr,

//    Year-to-date flags

inyeartodate(PeriodDate, \$(vToday), 0,4) * -1 AS YTD,                        //Current year-to-date

inyeartodate(PeriodDate, \$(vToday), -1,4) * -1 AS PYTD,                    //First prior year-to-date

inyeartodate(PeriodDate, \$(vToday), -2,4) * -1 AS SPYTD,                    //Second prior year-to-date

//    Quarter-to-date flags

inquartertodate(PeriodDate, \$(vToday), 0) * -1 AS QTD,                    //Current quarter-to-date

inquartertodate(PeriodDate, \$(vToday), -1) * -1 as PQTD,                    // Previous quarter-to-date,

//    inquartertodate(PeriodDate, \$(vToday), -4) * -1 AS FPQTD,                //First prior quarter-to-date, same quarter last year

//    inquartertodate(PeriodDate, \$(vToday), -8) * -1 AS SPQTD,                //Second prior quarter-to-date, same quarter two years ago

//    Month-to-date flags

inmonthtodate(PeriodDate, \$(vToday), 0) * -1 AS MTD,                    //Current month-to-date

inmonthtodate(PeriodDate, \$(vToday), -1) * -1 AS PMTD,                    //Previous month-to-date

//    inmonthtodate(PeriodDate, \$(vToday), -12) * -1 AS FPMTD,                    //First prior month-to-date, same month last year

//    inmonthtodate(PeriodDate, \$(vToday), -24) * -1 AS SPMTD,                    //Second prior month-to-date, same month two years ago

//    Week-to-date flags (use addmonths() to ensure proper week)

//    Use inlunarweek() if the you want weeks (7-day periods) starting from January 1. This is different from the inweek() function

inweektodate(PeriodDate, \$(vToday), 0) * -1 AS WTD,                        //Current week-to-date

inweektodate(PeriodDate, \$(vToday), -1) * -1 AS PWTD,                    //Previous week-to-date

//    inweektodate(PeriodDate, addmonths(\$(vToday), -12), 0) * -1 AS FPWTD,    //First prior week-to-date

//    inweektodate(PeriodDate, addmonths(\$(vToday), -24), 0) * -1 AS SPWTD,    //Second prior week-to-date

//    Day flags (use addmonths() to ensure proper day)

inday(PeriodDate, \$(vToday), 0) * -1 AS TD,                                //Current day

inday(PeriodDate, \$(vToday), -1) * -1 AS PD,

if((today()-date(PeriodDate))>=0 and (today()-date(PeriodDate))<=30,'0-30',

if((today()-date(PeriodDate))>30 and (today()-date(PeriodDate))<=60,'31-60',

if((today()-date(PeriodDate))>60 and (today()-date(PeriodDate))<=90,'61-90',

if((today()-date(PeriodDate))>90 and (today()-date(PeriodDate))<=120,'91-120',

if((today()-date(PeriodDate))>120 and (today()-date(PeriodDate))<=180,'121-180',

if((today()-date(PeriodDate))>180 and (today()-date(PeriodDate))<=360,'181-360',

if((today()-date(PeriodDate))>360 and (today()-date(PeriodDate))<=720,'361-720','Above 720'))))))) as Age                                  //Previous Day

RESIDENT TempCalendar Order By PeriodDate Asc;

• ###### Re: No Of Days For Selected Period?

Hi,

Check the attached file. It is return only your selection based days count

• ###### Re: No Of Days For Selected Period?

Hi Natarajan,

Its good file, but my calnder is created in INLINE table n associated with Month field of Data,

Quarter:

[

Quarter, MonthText

Q1,'Apr',

Q1,'May',

Q1,'Jun',

Q2,'Jul',

Q2,'Aug',

Q2,'Sep',

Q3,'Oct',

Q3,'Nov',

Q3,'Dec',

Q4,'Jan',

Q4,'Feb',

Q4,'Mar'

];

MonthText:

[

Month, MonthText, Days

1,'Apr', 30

2,'May', 31

3,'Jun', 30

4,'Jul', 31

5,'Aug', 31

6,'Sep', 30

7,'Oct', 31

8,'Nov', 30

9,'Dec', 31

10,'Jan', 31

11,'Feb', 28

12,'Mar', 31

];

and given assocaition with this

num(Month(MakeDate(left(MNTH,4),pick(num(Right(MNTH,2)),

10,

11,

12,

01,

02,

03,

04,

05,

06,

07,

08,

09)

,01))) as Month,

here MNTH is my month field,

then how can i achive that NoOfDays in my script

• ###### Re: No Of Days For Selected Period?

Hello anybody is there to sortout problem, Its very urgent.

• ###### Re: No Of Days For Selected Period?

Hi,