4 Replies Latest reply: Mar 19, 2016 5:31 AM by Hirish V RSS

    year, month ?

    Helen Betty

      HI All,

       

      Am having Year(2016) , Month(Jan,Feb,Mar & So on..) in My table. Need to connect my master Calendar, How can i connect this.

      Can any provide me the Script..

       

      Regards,

      Helen

        • Re: year, month ?
          kushal chawda

          Do you have date in your data?

           

          For now create the Field in your data and master calendar as below and link on that field

           

          =date(date#(Year&Month,'YYYYMMM'),'MMM-YYYY') as Link

          • Re: year, month ?
            Agnivesh Kumar

            If you have Year and Month field in your data , then you need to make date range from that in load script,

            using YearStart and YearEnd

            • Re: year, month ?
              Maurice Wehbe

              Hi Helen,

               

              If you have multiple sub date fields in your FACTS table, you better concat them as a single date format to avoid Syntetic keys with your master calendar.

               

              So first concat them in Facts table to have only one date KEY (full date using first day of each month or just yearmonth format if enough for you)

              Then have the same date KEY generated in your Mastercalendar which could also have sub dates fields for analysis (Year, Month....):

               

              MasterCalendar:

              LOAD

                date(date#(Year&Month, 'YYYYMMM'), 'MMM-YYYY') AS MonthYear,

                 ...

                 ...

               

              Best,

              Maurice

              • Re: year, month ?
                Hirish V

                Hi,

                I hope your Data is like this,

                 

                Data:
                LOAD *,
                Date(Date#('01'&'/'& Month & '/'&Year,'DD/MMM/YYYY'),'DD/MM/YYYY') as Date
                
                
                INLINE [
                    Month, Year, Data, Customer
                    Jan, 2016, 423, A
                    Feb, 2016, 233, B
                    Mar, 2016, 33, C
                    Apr, 2016, 23, D
                    May, 2016, 333, E
                    Jun, 2016, 332, F
                    Jul, 2016, 33, G
                    Aug, 2016, 54, H
                    Sep, 2016, 544, I
                    Oct, 2016, 4542, J
                    Nov, 2016, 433, K
                    Dec, 2016, 22, L
                ];
                
                
                
                
                

                 

                 

                Then by using Calendar Script ,Will have the output like this,

                year& month-Date-210159.PNG

                 

                Hope this helps ,

                PFA,

                Hirish