4 Replies Latest reply: Aug 25, 2013 5:37 PM by Francesco Ravagnolo RSS

    MAX Date based on other information

      Dear all,

       

      I'm tring to identify and set a variable based of the MAX Date included in a fact table for my actuals....this without success.

       

      Fact table:

      KEYDATEBudgetActuals
      AAA01/01/201313€13€
      BBB01/02/201313€13€
      CCC01/03/201313€
      DDD01/04/201313€
      EEE01/05/201313€

       

      I'm actually looking for a MAX formula returning (in my exemple) 01/02/2013.


      Thanks

        • Re: MAX Date based on other information
          whiteline _

          Hi.

           

          In this case you could use set analysis to calculate MAX only for the rows filled with actual value.

          Something like this: =Max({<Actuals={'*'}>} DATE)

            • Re: MAX Date based on other information

              It is not working for me. I modified it as (max({<ACTps = {0}>} Date) as TDDate ) where ACTps is the name of the ACTUAL Field.


              I'm attachign the full script I'm tring to set-up.

               

               

              ------------START_________________

               

              MAPPING LOAD                                                                          

              rowno() as Month,                                                                     

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

              AUTOGENERATE (12);                                                                    


              Temp:                                                                                 

              Load                                                                                  

                            
              min(Date) as minDate,                                             

                            
              max(Date) as maxDate,  

                            
              max({<ACTps = {0}>} Date) as TDDate                                     

              Resident
              [FACT];                                                                      

                                                                                                    

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

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

              Let varTD = Num(Peek('TDDate', 0, 'Temp'));                                  

              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 Date,                                                                                           

                            
              Year(TempDate) As Year,                                                

                            
              Month(TempDate) As Month,                                                                                              

                            
              InMonth (TempDate, $(varTD),0)*-1 as CMTDFlag,

                            
              InMonth (TempDate, $(varTD),-1)*-1 as LMTDFlag,

                            
              InQuarter (TempDate, $(varTD),0)*-1 as CQTDFlag,

                            
              InQuarter (TempDate, $(varTD),-1)*-1 as LQTDFlag,

                            
              InYearToDate (TempDate, $(varTD),0)*-1 as CYTDFlag,

                            
              InYearToDate (TempDate, $(varTD),-1)*-1 as LYTDFlag,                             

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

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

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

              Resident TempCalendar                                                                 

              Order By TempDate ASC;                                                                

              Drop Table TempCalendar;                                                              

               

                • Re: MAX Date based on other information
                  whiteline _

                  Sorry, my solution is for front-end, not for script.

                   

                  In Script just use 'where' statement and separate load for TDDate.

                   

                  Temp:                                                                                 
                  Load                                                                                  
                                
                  min(Date) as minDate,                                             
                                
                  max(Date) as maxDate

                  Resident [FACT];      


                  temp2:

                  Load                                                                                  
                                
                  max
                  (Date) as TDDate                                     
                  Resident
                  [FACT]

                  where ACTps<>0;