No Of Days For Selected Period?

[khadeer]

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

[SunilChauhan]

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

[khadeer]

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

[kumarnatarajan]

Hi,

Please use the following syntax

Count([Day (#)])

[Day (#)] as your day field

[khadeer]

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

[kumarnatarajan]

Hi,

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

[khadeer]

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

[perumal_41]

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

[kumarnatarajan]

Hi,

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

[khadeer]

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