6 Replies Latest reply: Feb 14, 2014 10:34 AM by David Young RSS

    Base Month & Year

    David Young

      I have 500 clients that use one of our products.  When you setup our products there is a field label:

       

      1.  Start Base Month & Year

       

      These can be different depending on the client but typically they are July, Aug, Sept of some year.  What I want to do is to normalize my data for a application I am building.  The clients enter 12 months of data.   In order accomplish this I need to set the Start Base Month & Year to an Index of 1.  Can this be done.  Here is a sample of what the output can look like:

       

      Account NumberAccount NameStart  Base MonthStart  Base YearFirst Month Index
      1A720011
      2B820051
      3C720081
      4D920101

       

      Can this be done?

       

      David

        • Re: Base Month & Year
          Ajay Krishnan Prabhakaran

          Hey David,

          So if you want to create a flag for those months, then yes its absolutely possible, but just make sure you define it as per requirements like for 2001 its Aug, 2002 Sep, etc.

          There should be some logic as to how this base month for each year is defined. Without this logic say for next year to assign a month you would have to go and manually code it.

           

          Hope it helps.

           

          Thanks

          AJ

            • Re: Base Month & Year
              David Young

              I dont understand.  Is there a way to show me what you are talking about.

                • Re: Base Month & Year
                  John Cavoulas

                  Hi David,

                   

                  Questions on the end goal:

                   

                  What will you compare the First Month Index to?

                   

                  Is it to see how long a product has been in use? (basically the same as above)

                   

                  Can there be more than one entry per customer?

                   

                  To me there's not enough info to model your data, but yes, you can do what you describe.

                   

                  --john

                    • Re: Base Month & Year
                      David Young

                      Hi John,

                       

                      There cant be more than one Fiscal Year Begin Date per client.  The reason I need this is to Fiscal Year Comparisons.  I need to normalize all the months so I have a Fiscal Month 1,2,3,4,etc...  and the month is relative to the Fiscal Year Start Date...so for client a, b, c can have different start dates (july, sept, jan)...Fiscal Month 1 should refer to july, sept, or Jan. depending on their FY setup.  Does this help and if so can you show me how to accomplish this.

                       

                      David

                        • Re: Base Month & Year
                          Srikanth P

                          Hi David, do you the which client have which Fiscal Year in other table ? If yes, this would be absolutely possible

                           

                          Please post both sample source data that would be good for understanding

                            • Re: Base Month & Year
                              David Young

                              I am not sure what you are asking but here is what I have:

                               

                              Load AccountNumber,

                              AccountName,

                              FiscalYearStartDate

                              BillStartDate

                              from

                              account.qvd;

                               

                              From there I want to show:

                               

                              AccountNumber 1

                              AccountName - XYZ

                              BillStartDate - 1/1/2013

                              FiscalYearStartDate - 7/1/2012

                              Month(FiscalYearStartDate) as Month - 7

                              Month Index 1 since 7/1/2012 is the FiscalYearStartDate

                               

                              AccountNumber 2

                              AccountName - ABC

                              BillStartDate - 2/1/2013

                              FiscalYearStartDate - 8/1/2012

                              Month(FiscalYearStartDate) as Month - 8

                              Month Index 1 since 8/1/2012 is the FiscalYearStartDate

                               

                              Does this make since?

                               

                              David