10 Replies Latest reply: Oct 28, 2015 10:47 PM by jagan mohan rao appala RSS

    Monthly Run Rate

    Kumar Pramod

      Hi all,

       

      jagan

       

      I need a data from start of this month to yesterday.

      count({$<CanonicalDate={'>$(=Date(Today()-20))<=$(=Today()-1)'}, DateType= {'invitation'}>} invitation_id)

       

      and also i need to compare this value with the last month value how can i do this.

       

      see the attached sample qvf file, The issue is i don't have recent dates data in the .qvf file.

       

       

      Regards,

      Pramod

        • Re: Monthly Run Rate
          Manish Kachhia

          For Current Month

          =Count({<

               CanonicalDate = {">=$(=Date(MonthStart(Today())))<=$(=Date(Today()))"},

               DateType = {'invitation'}

               >}invitation_id)

           

          For Previous Month

          =Count({<

               CanonicalDate = {">=$(=Date(MonthStart(Today(),-1)))<=$(=Date(AddMonths(Today(),-1)))"},

               DateType = {'invitation'}

               >}invitation_id)

            • Re: Monthly Run Rate
              Kumar Pramod

              This fine for individual data.

              But  i need comparison of both and i need in percentage.

              whether this month data is increased or decreased when compared to last month.

                • Re: Monthly Run Rate
                  Jonathan Dienst

                  Well Manish has given you the current month and prior month already. Just combine these to get the % change and to determine whether the value has increased or decreased.

                  • Re: Monthly Run Rate
                    jagan mohan rao appala

                    Hi Kumar,

                     

                    Updated Manish's expression

                     

                    =If(Count({<

                         CanonicalDate = {">=$(=Date(MonthStart(Today())))<=$(=Date(Today()))"},

                         DateType = {'invitation'}

                         >}invitation_id)/

                    Count({<

                         CanonicalDate = {">=$(=Date(MonthStart(Today(),-1)))<=$(=Date(AddMonths(Today(),-1)))"},

                         DateType = {'invitation'}

                         >}invitation_id) > 1, 'Up', 'Down')

                     

                    Hope this helps you.

                     

                    Regards,

                    Jagan.

                      • Re: Monthly Run Rate
                        Kumar Pramod

                        Hi jagan,

                         

                        Is not possible to show +65% or -35% like this, Instead of Up and Down.

                         

                        Thanks,

                        Pramod

                          • Re: Monthly Run Rate
                            jagan mohan rao appala

                            Hi,

                             

                            Try like this

                             

                            =Num((Count({<

                                 CanonicalDate = {">=$(=Date(MonthStart(Today())))<=$(=Date(Today()))"},

                                 DateType = {'invitation'}

                                 >}invitation_id) -

                            Count({<

                                 CanonicalDate = {">=$(=Date(MonthStart(Today(),-1)))<=$(=Date(AddMonths(Today(),-1)))"},

                                 DateType = {'invitation'}

                                 >}invitation_id) > 1, 'Up', 'Down'))

                            /

                            Count({<

                                 CanonicalDate = {">=$(=Date(MonthStart(Today(),-1)))<=$(=Date(AddMonths(Today(),-1)))"},

                                 DateType = {'invitation'}

                                 >}invitation_id), '#,##0%')

                             

                            Formula: = (CurrentMonth - PreviousMonth)/PreviousMonth

                             

                            Hope this helps you.

                             

                            Regards,

                            Jagan.

                              • Re: Monthly Run Rate
                                Kumar Pramod

                                jagan it's printing  #,##0%

                                • Re: Monthly Run Rate
                                  Kumar Pramod

                                  Hi jagan,

                                   

                                  I am using the Expression like below,

                                   

                                  is this works?

                                   

                                  if(dims='Invitations',Num((count({$<CanonicalDate = {">=$(=Yearstart(Max(CanonicalDate)))  <= $(=Date(Max(CanonicalDate)))"}, DateType= {'invitation'}>} invitation_id)-

                                     count({$<CanonicalDate = {">=$(=Yearstart(Max(CanonicalDate), -1))  <= $(=YearEnd(Max(CanonicalDate)))"}, DateType= {'invitation'}>} invitation_id)> 1, 'Up', 'Down'))/

                                     count({$<CanonicalDate = {">=$(=Yearstart(Max(CanonicalDate), -1))  <= $(=YearEnd(Max(CanonicalDate)))"}, DateType= {'invitation'}>} invitation_id), '#,##0%'), 

                                   

                                   

                                  if(dims='Landing Page Hits', sum({$<CanonicalDate = {">=$(=Yearstart(Max(CanonicalDate)))  <= $(=Date(Max(CanonicalDate)))"}, DateType= {'accessed'}>} landing_page_accessed)/

                                     sum({$<CanonicalDate = {">=$(=Yearstart(Max(CanonicalDate), -1))  <= $(=YearEnd(Max(CanonicalDate)))"}, DateType= {'accessed'}>} landing_page_accessed)))

                                    • Re: Monthly Run Rate
                                      kushal chawda

                                      try this,

                                       

                                      =Pick(match(dims,'Invitations','Landing Page Hits'),

                                      Num((count({$<CanonicalDate = {">=$(=Monthstart(Max(CanonicalDate)))  <= $(=Date(Max(CanonicalDate)))"}, DateType= {'invitation'}>} invitation_id)-

                                         count({$<CanonicalDate = {">=$(=Monthstart(Max(CanonicalDate), -1))  <= $(=MonthEnd(Max(CanonicalDate),-1))"}, DateType= {'invitation'}>} invitation_id))/

                                         count({$<CanonicalDate = {">=$(=Monthstart(Max(CanonicalDate), -1))  <= $(=MonthEnd(Max(CanonicalDate),-1))"}, DateType= {'invitation'}>} invitation_id), '#,##0%'),

                                        

                                      Num((count({$<CanonicalDate = {">=$(=Monthstart(Max(CanonicalDate)))  <= $(=Date(Max(CanonicalDate)))"}, DateType= {'accessed'}>} landing_page_accessed)-

                                         count({$<CanonicalDate = {">=$(=Monthstart(Max(CanonicalDate), -1))  <= $(=MonthEnd(Max(CanonicalDate),-1))"}, DateType= {'accessed'}>} landing_page_accessed))/

                                         count({$<CanonicalDate = {">=$(=Monthstart(Max(CanonicalDate), -1))  <= $(=MonthEnd(Max(CanonicalDate),-1))"}, DateType= {'accessed'}>} landing_page_accessed), '#,##0%'))

                                      • Re: Monthly Run Rate
                                        jagan mohan rao appala

                                        Hi,

                                         

                                        Your expression is entirely different than mine.  Correct your expression.

                                         

                                        Give your expression like this

                                         

                                        Formula: = (CurrentMonth - PreviousMonth)/PreviousMonth

                                         

                                        Note: Replace CurrentMonth with your current month formula and PreviousMonth with previous month formula.

                                         

                                        Regards,

                                        jagan.