Current vs. Previous Month

[#1]

I am trying to calculate Previous month invoice amount compared to current month invoice amount.

I am using SUM(INVC_ITEM_AMT) to get my current month invoice charges

How do I get my Previous month invoice charges?

Thank you,

dawn

[nirav_bhimani]

Hi,

Used the following expression

For Current Month Sales:

=sum({<CalendarYear={$(=max(CalendarYear))},CalendarMonthName={$(=Month(Max(InvDate)))}>} INVC_ITEM_AMT)

For Last Month Sales:

=sum({<CalendarYear={$(=max(CalendarYear))},CalendarMonthName={$(=month(addmonths((max(InvDate)),-1)))}>}INVC_ITEM_AMT)

Regards,

Nirav Bhimani

[#3]

Thanks for your reply.

I used the calculations above and got Current Month Information for both columns.  Is there something else I need to do in order for it to calculate properly?

dawn

[Sokkorn]

Hi Dawn,

I would suggest to create a new field (bold) for easy control in set analysis, something like

[Table]:

LOAD

...

[InvoiceDate],

[INVC_ITEM_AMT],

Year([InvoiceDate])*12 + Month([InvoiceDate]) AS [%YM_Seq],

...

Then in expression, we can try

For current month invoice amount

=Sum({$<[%YM_Seq] = {$(=Max([%YM_Seq]))}>} [INVC_ITEM_AMT])

For previous month invoice amount

=Sum({$<[%YM_Seq] = {$(=Max([%YM_Seq])-1)}>} [INVC_ITEM_AMT])

For last three month invoice amount

=Sum({$<[%YM_Seq] = {$(=Max([%YM_Seq])-3)}>} [INVC_ITEM_AMT])

Regards,

Sokkorn

[narender123]

Hi,

Try this.

for Current Month

=SUM({<Year={$(=Max(Year))},Month={$(=MaxString(Month))}>}INVC_ITEM_AMT)

or

=SUM({<Year={$(=Max(Year))},Month={$(=MonthString(Today()))}>}INVC_ITEM_AMT)

for Previous Month

=SUM({<Year={$(=Max(Year))},Month={$(=Max(Month)-1)}>}INVC_ITEM_AMT)

or

=SUM({<Year={$(=Max(Year))},Month={$(=num(month(Today())-1))}>}INVC_ITEM_AMT)

Thanks.