topic Re: No Of Days For Selected Period? in QlikView

No Of Days For Selected Period?

khadeer — Mon, 28 Jan 2013 06:21:27 GMT

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?

SunilChauhan — Mon, 28 Jan 2013 06:39:00 GMT

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?

khadeer — Mon, 28 Jan 2013 06:44:34 GMT

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?

kumarnatarajan — Mon, 28 Jan 2013 06:51:29 GMT

Hi,

Please use the following syntax

Count([Day (#)])

[Day (#)] as your day field

Re: No Of Days For Selected Period?

khadeer — Mon, 28 Jan 2013 06:59:06 GMT

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?

kumarnatarajan — Mon, 28 Jan 2013 07:10:27 GMT

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?

khadeer — Mon, 28 Jan 2013 07:14:43 GMT

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?

perumal_41 — Mon, 28 Jan 2013 07:19:56 GMT

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:

Load Recno() as DateKey,

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:

Mapping LOAD * Inline [

Qtr,QtrMap

Q1,Q4

Q2,Q1

Q3,Q2

Q4,Q3

];

Calendar:

LOAD Date(MakeDate(year(PeriodDate),month(PeriodDate),day(PeriodDate)),'DD/MM/YYYY') AS Date,

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;

,then Count (Date) ;You get answer.please try

Re: No Of Days For Selected Period?

kumarnatarajan — Mon, 28 Jan 2013 07:20:47 GMT

Hi,

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

Re: No Of Days For Selected Period?

khadeer — Mon, 28 Jan 2013 07:58:04 GMT

Hi Natarajan,

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

Quarter:

load * inline

[

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:

load * inline

[

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?

khadeer — Mon, 28 Jan 2013 09:28:45 GMT

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

Re: No Of Days For Selected Period?

kumarnatarajan — Mon, 28 Jan 2013 09:43:57 GMT

Hi,

Please find the attachment.