4 Replies Latest reply: Feb 9, 2016 10:08 AM by Mitali Ajgaonkar RSS

    Mastercalender to date column mapping

    Mitali Ajgaonkar

      Hi,

      I have created a master calender for the date range i have data. I have data of tweets.i want a bar chart having dimension of all the days in a week and another one with month names. it should measure  the no of tweets in a week and month. how do i link this to master calendar?

      p1.png

      This is my new date column which i want to link to master calendar. the bar graph should show height 1 on 02/05/2016 and height zero for 01/04/2016 since there is 02/04/2016 in this newdate column.

        • Re: Mastercalender to date column mapping
          Henric Cronström

          Just make sure that your master calendar contains the field "newdate", a Month field and a Week field, and it should work automatically.

           

          HIC

            • Re: Mastercalender to date column mapping
              Mitali Ajgaonkar

              QuartersMap: 

              MAPPING LOAD  

              rowno() as Month, 

              'Q' & Ceil (rowno()/3) as Quarter 

              AUTOGENERATE (12); 

               

              Temp: 

              Load 

                           min(newdate) as minDate, 

                             max(newdate) as maxDate

              Resident  root; 

               

               

              Let varMinDate = num(makedate(2014,02,08));

              //Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

              //Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

              //LET varMaxDate=floor(monthend(today()));

              LET varMaxDate=floor(today());

              DROP Table Temp; 

               

              TempCalendar: 

              LOAD 

                             $(varMinDate) + Iterno()-1 As Num, 

                             Date($(varMinDate) + IterNo() - 1) as TempDate 

                             AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

               

              MasterCalendar: 

              Load 

                             TempDate AS OrderDate, 

                             week(TempDate) As Week, 

                             Year(TempDate) As Year, 

                             Month(TempDate) As Month, 

                             Day(TempDate) As Day, 

                             YeartoDate(TempDate)*-1 as CurYTDFlag, 

                             YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

                             inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

                             date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

                             ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

                             Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

                             WeekDay(TempDate) as WeekDay 

              Resident TempCalendar 

              Order By TempDate ASC; 

              Drop Table TempCalendar; 

               

               

              how to add new date in master calender?

            • Re: Mastercalender to date column mapping
              Mark Little

              Hi,

               

              Range:

              LOAD

                min([newdate]) as startdate,

                max([newdate]) as enddate

              resident YourTable';

               

              //Peek out the values for later use

              let vStart = peek('startdate',-1,'Range')-1;

              let vEnd = peek('enddate',-1,'Range');

              let vRange = $(vEnd) - $(vStart);

               

              //Remove Range table as no longer needed

              Drop table Range;

               

              //Generate a table with a row per date between the range above

              Date:

              Load

                $(vStart)+recno() as Date

              autogenerate $(vRange);

               

              //Calculate the Parts you need to examine

              Calendar:

              load

                Date as [newdate],

                Year(Date) as CalendarYear,

                Month(Date) as Cal_Month,

                Day(Date) as Cal_Day,

                Week(Date) as Cal_Week,

                Weekday(Date) as Cal_WeekDay

              resident Date;

               

              //Tidy up

              Drop table Date;