Monthly Run Rate

[#1]

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

[MK_QSL]

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)

[#3]

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.

[jonathandienst]

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.

[jagan]

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.

[#6]

Hi jagan,

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

Thanks,

Pramod

[jagan]

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.

[#8]

jagan it's printing  #,##0%

[#9]

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)))

[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%'))