10 Replies Latest reply: Feb 12, 2018 11:27 AM by Vatsal Ajmera RSS

    Current Month vs Last Month

      Folks,

       

      I'm trying to build a table with 2 collumns: Current Month Sales | Past Month Sales.

       

      Could you help me with the expression to create the Past Month Collumn ?

       

      Thanks

      Vinicius

        • Re: Current Month vs Last Month

          HI ,  

                For Past Month try this code :

          if(Month(Date)=month(AddMonths(Today(),-1)), Sales ) as PastMonthSales

           

          //Yusuf

          • Re: Current Month vs Last Month
            Neha Deshmukh

            Hi,

             

            Try this Expression for last month data-

             

            =sum({<Year={$(=max(Year))},Month={$(=month(addmonths((max(Date)),-1)))}>}Sales)

             

             

            Regards,

            Neha

            • Re: Current Month vs Last Month
              ajeet tomer

              Hi, Vinicius

               

              Create two variables

              1. vCurrentMonth

              =Max(Month)

              and make ur expression like:

              sum( {$<Year = {$(#vCurrentMonth)}>} Sales )

               

               

               

              2. vLastMonth

              =Max(Month-1)

              sum( {$<Year = {$(#vLastMonth)}>} Sales )

               

               

              Same as u can create Current Year and Last Year calculation

               

               

               

              Regards

              Ajeet Tomer

              • Re: Current Month vs Last Month
                Israr Khan

                Hi,

                 

                Use =sum(Amount) for current month.

                 

                Use =sum({<Month={$(=Max(Month)-1)}>} Amount) for last Month.

                 

                it will work on your current selections:

                 

                Hope this help.

                 

                Kind Regards

                 

                Khan

                • Re: Current Month vs Last Month
                  Alan Farrell

                  Try

                   

                  Current Month:

                   

                  SUM({<DateField ={">=$(=MonthStart(Today())) <=$(=MonthEnd(Today()))"}>} Amount)

                   

                  Previous Month:

                   

                  SUM({<DateField ={">=$(=MonthStart(Today(),-1)) <=$(=MonthEnd(Today(),-1))"}>} Amount)

                   

                  In this Example if the DateField was  [Calendar Date] it would look like this

                   

                  SUM({<[Calendar Date] ={">=$(=MonthStart(Today())) <=$(=MonthEnd(Today()))"}>} Amount)

                   

                  SUM({<[Calendar Date] ={">=$(=MonthStart(Today(),-1)) <=$(=MonthEnd(Today(),-1))"}>} Amount)

                   

                  Let me know if this works

                   

                  Regards

                   

                  Alan


                   



                    • Re: Current Month vs Last Month

                      I found the following to work more accurately for me.

                       

                      =count ({<ClaimsAssisstantSubmittedDate ={">=$(=MonthStart(Today(),-1)) <$(=MonthStart(Today()))"}>} JobID)

                       

                       

                      The difference:

                      Alan's code has <= monthend(today,-1);

                      I have                < monthstart(today)

                    • Re: Current Month vs Last Month
                      Vatsal Ajmera

                      Hi,

                      The easiest solution to this would be to the monthAgo calculation in the Qlik Calender.

                      create an auto calendar for the date field you are trying to visualize by using the Derive fields function in the calendar script. ( I can attach the complete auto calendar script if needed)

                       

                      ex:-  DERIVE FIELDS FROM FIELDS [date Field]

                       

                      now, for current month the code in the measure field would be :

                      Sum( {$ < [Date.autoCalendar.MonthsAgo] = {'0'}>} sales) 

                      U can change the 0 to 1,2,3 to go back to n last months.

                       

                      Hope this helps.

                        • Re: Current Month vs Last Month
                          Murat Akkemik

                          could you please send me auto calendar full script?

                          thx

                          Murat

                            • Re: Current Month vs Last Month
                              Vatsal Ajmera

                              Hi,

                               

                              Below is the query for the auto Calender. Remeber this is for QLIKSense. Load the script and use the Derive function to add the fields for which you want the auto calender. Let me know if it works for you.

                               

                               

                              [autoCalendar]:
                                DECLARE FIELD DEFINITION Tagged ('$date')
                              FIELDS
                                Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
                                Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
                                Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
                                Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
                                Month($1) AS [Month] Tagged ('$month', '$cyclic'),
                                Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
                                Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
                                Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
                                Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
                                Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
                                If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
                                Year(Today())-Year($1) AS [YearsAgo] ,
                                If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
                                4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
                                Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
                                If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
                                12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
                                Month(Today())-Month($1) AS [MonthRelNo] ,
                                If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
                                (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
                                Week(Today())-Week($1) AS [WeekRelNo] ;

                              DERIVE FIELDS FROM FIELDS [DATE FIELD] USING [autoCalendar] ;