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

    No Of Days For Selected Period?

    Khadeer Shaik

      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?
          Sunil Chauhan

          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?
            Kumar Natarajan

            Hi,

             

            Please use the following syntax

             

            Count([Day (#)])

             

            [Day (#)] as your day field

              • Re: No Of Days For Selected Period?
                Khadeer Shaik

                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?
                    Kumar Natarajan

                    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 Shaik

                        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 Ayyappan

                            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?
                              Kumar Natarajan

                              Hi,

                               

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

                                • Re: No Of Days For Selected Period?
                                  Khadeer Shaik

                                  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