16 Replies Latest reply: Sep 14, 2017 1:41 AM by bhadraka herarth RSS

    Previous month value

    Ashis Sau

      Hello,

       

      I have NumMonth field in my master calendar, and I am using the following code to generate NumMonth.

       

      num(Month(TempDate)) As NumMonth

       

       

      Now I want to get previous months count m based on my month selection. I am using this code.

       

      =count({$< NumMonth={"=num(max(NumMonth))-1"}>} Reconciliation_ID)

       

      This code is giving me current months count. if I hard code the value as

       

      =count({$< NumMonth={5}>} Reconciliation_ID)  I get the result . however it is not working with the code I am using.

       

      Please help

       

      Thank you,

      Ashis

        • Re: Previous month value
          Anil Babu

          You can use this?

           

          =count({$< NumMonth={'$(=Max(NumMonth)-1)'}>} Reconciliation_ID)

            • Re: Previous month value
              Ashis Sau

              Thank your for your reply,

               

              It is giving me 0 as value.

                • Re: Previous month value
                  Jose Miguel Vilaplana

                  Hi,

                   

                  If you put the formula (=num(max(NumMonth))-1) in a label, what's the result? is 5?


                  Regards

                    • Re: Previous month value
                      Ashis Sau

                      I am printing it in a text box

                      so if I put your code it is giving me 7 (numeric value ) if the month Aug(8) is selected.

                        • Re: Previous month value
                          Sunny Talwar

                          I would think this to work from Anil

                          =Count({$<NumMonth={'$(=Max(NumMonth)-1)'}>} Reconciliation_ID)

                           

                          With a slight issue that what would you want to see when Jan is selected?

                            • Re: Previous month value
                              Ashis Sau

                              Hi Sunny,

                               

                              Thanks for your reply. That is very valid point. It leads me to think I should not use numeric value for month.

                               

                              I tried the following code also

                              =count({<Month={"$(=Date(AddMonths(max(Month),-1),'MMM'))"}>} Reconciliation_ID)

                               

                              I am facing one serious issue , please see my below code for master calendar.

                              For month calculation if I use  Month(TempDate) As Month , I find it difficult to calculate such as month , previous month .

                              however if I use Date(MonthStart(TempDate),'MMM') as Month  ,  I find it give correct result in set analysis.

                               

                               

                              in my current scenario that I am working on , following code Date(MonthStart(TempDate),'MMM') as Month  is giving me duplicate month values such as Aug twice(since I have two years and in both the years I have few common months) , in my list box which I do not want.

                               

                              I will be forever grateful if you let me know which formula to follow in master calendar.

                              --------------------------------------------------------

                              QuartersMap: 
                              MAPPING LOAD  
                              rowno() as Month
                              'Q' &
                              Ceil (rowno()/3) as Quarter 
                              AUTOGENERATE (12); 

                              Temp: 
                              Load 
                              min(Date) as minDate
                              max(Date) as maxDate 
                              Resident ExceptionDataFeedRun; 

                              Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
                              Let varMaxDate = Num(Peek('maxDate', 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)

                               

                              asterCalendar: 
                              Load 
                              TempDate AS [Date],
                              week(TempDate) As Week
                              Year(TempDate) As Year
                              Month(TempDate) As Month,
                              Date(MonthStart(TempDate),'MMM-YY') as YearMonth,
                              //Date(MonthStart(TempDate),'MMM') as Month,
                                num(Month(TempDate)) As NumMonth,
                              Day(TempDate) As Day
                              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; 

                                • Re: Previous month value
                                  Sunny Talwar

                                  If that works for you, go for it... but I would probably use YearMonth field from your calendar to do this

                                   

                                  Date(MonthStart(TempDate),'MMM-YY') as YearMonth,

                                   

                                  Something like this

                                  =Count({$<YearMonth = {"$(=Date(MonthStart(Max(YearMonth), -1), 'MMM-YY'))"}, NumMonth, Week, Year, Month, Date, Day, Quarter, WeekYear, WeekDay>} Reconciliation_ID)

                      • Re: Previous month value
                        Momin Tahemas

                        Hi

                        Please try this

                        =count({$< NumMonth={'$1(=Max(NumMonth)-1)'}>} Reconciliation_ID)

                         

                         

                        Regards

                        Tahemas Momin

                        • Re: Previous month value
                          Momin Tahemas

                          To get the previous month of current selection

                          =num(max({$}MonthNum))-1

                           

                          to get the previous Selection

                          =num(max({$1}MonthNum))-1

                           

                          Regards

                          Tahemas Momin

                          • Re: Previous month value
                            Brice SACCUCCI

                            Hello,

                             

                            please add this field to your model : Year(Date) * 12 + Month(Date) as [Month Counter].

                             

                            And then your set analysis becomes [Mounth Counter] = {'$(=MAX([Month Counter] - 1))'}.

                             

                            Il you use Num Month, you will have an issue in January. The solution I propose goes to December of the previous year.

                             

                            Thanks,

                            Brice

                              • Re: Previous month value
                                Ashis Sau

                                Hi Brice,

                                 

                                Thank you for your reply. I added your code in my script, please see the attach code.

                                How ever id did not work,

                                 

                                =count({$< MonthCounter={'$(=MAX(MonthCounter) - 1)'}>} Reconciliation_ID)

                                 

                                Master calendar:

                                -------------------

                                QuartersMap: 
                                MAPPING LOAD  
                                rowno() as Month
                                'Q' &
                                Ceil (rowno()/3) as Quarter 
                                AUTOGENERATE (12); 

                                Temp: 
                                Load 
                                min(Date) as minDate
                                max(Date) as maxDate 
                                Resident ExceptionDataFeedRun; 

                                Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
                                Let varMaxDate = Num(Peek('maxDate', 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],
                                week(TempDate) As Week
                                Year(TempDate) As Year
                                Month(TempDate) As Month,
                                Date(MonthStart(TempDate),'MMM-YY') as YearMonth,
                                //Date(MonthStart(TempDate),'MMM') as Month,
                                  Year(TempDate) * 12 + Month(TempDate) as [MonthCounter],
                                num(Month(TempDate)) As NumMonth,
                                Day(TempDate) As Day
                                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; 


                                  • Re: Previous month value
                                    Brice SACCUCCI

                                    I've forgotten a ')', sorry! I've edited my first answer

                                      • Re: Previous month value
                                        Ashis Sau

                                        Hi Brice,

                                         

                                        Please see my code, I have added your code in my Master Calender. then I used [Month Counter] in my set analysis

                                        =count({$< [Month Counter]={'$(=MAX([Month Counter] - 1))'}>} Reconciliation_ID)  or

                                         

                                        =count({$< [Month Counter]={'$(=MAX([Month Counter]-1))'}>} Reconciliation_ID) , however it is returning 0 value. Please guide.

                                         

                                        MasterCalendar: 
                                        Load 
                                        TempDate AS [Date],
                                        week(TempDate) As Week
                                        Year(TempDate) As Year
                                        Month(TempDate) As Month,
                                        Date(MonthStart(TempDate),'MMM-YY') as YearMonth,
                                        //Date(MonthStart(TempDate),'MMM') as Month,
                                          Year(TempDate) * 12 + Month(TempDate) as [Month Counter],
                                        num(Month(TempDate)) As NumMonth,
                                        Day(TempDate) As Day
                                        ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter
                                        Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear
                                        WeekDay(TempDate) as WeekDay 
                                        Resident TempCalendar 
                                        Order By TempDate ASC

                                         

                                         

                                         

                                         

                                    • Re: Previous month value
                                      Ashis Sau

                                      Hi Brice,

                                       

                                      Thank you for your guidance, it worked for me if I am calculating using variable.like the following script

                                       

                                      vPreviousMonthCounter=max([Month Counter]) -1

                                       

                                      =count({1<[Month Counter]={'$(vPreviousMonthCounter)'}>} Reconciliation_ID

                                       

                                      Thank you,

                                    • Re: Previous month value
                                      bhadraka herarth

                                      Count({$<NumMonth= {"<=$(=max(NumMonth))    >$(=max(NumMonth)-(5))"}>}DISTINCT NumMonth)