Comparing Current Month and Previous Month Sales

[#1]

Hi,

I wanted to compare the total sales amount of the current month to the total sales amount of the previous month.

I tried using the below expression, but the previous month script does not seems to work. It will still display as the current month figure:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LOAD Year,

     Month,

     Product,

     Sales,

     Date(Date#(Left(FileBaseName(), 10), 'MM/DD/YYYY')) As FileDate

FROM

(biff, embedded labels, table is Sheet1$);

Current Month Expression:

= sum({<FileDate = {">=$(=MonthStart(Today()))<= $(=MonthEnd(Today()))"}>}Sales)

Previous Month Expression:

= sum({<FileDate = {">=$(=MonthStart(AddMonths(Today(),-1)))<= $(=MonthEnd(AddMonths(Today(),-1)))"}>}Sales)

Please help. Appreciate your time and help in this.

Thank you.

[#2]

Hi Max,

Thanks! The inline statement works properly now. I guess it is missing the [ after Inline.

However, now the previous month field does not display any data when I select the month from the monthname.

The data only can be shown when the original month field is selected.

My expression for current and previous month stays the same:

Current Month Expression: sum({<Month={"$(=(Month))"},Year={"$(=(Year))"}>}Act_Amt)

Previous Month Expression: sum({<Month={"$(=(Month)-1)"},Year={"$(=(Year))"}>}Act_Amt)

Should i do any editing?

[PrashantSangle]

Hi,

check that you are creating link between between your original table and new inline table.

give same field name for month in both table.

[#4]

Hi Max,

there seems to be some error in the script:

[#5]

I think it is already linked:

[PrashantSangle]

Hi,

Sorry my bad,

try this

Sum({<MonthName={"$(=Month(Max(Date)-1)),Year={$(=(Year))"}>}Act_Amt)

[PrashantSangle]

Hi,

Sum({<MonthName={"$(=Month(Max(Date)-1))"},Year={"$(=(Year))"}>}Act_Amt)

[#8]

Hi Max,

It works now!

But the error in point 2 still exist:

2. The data for previous month of Jan i.e. Dec does not appear.

[PrashantSangle]

Hi,

For that you have to nullify your Year field.

So

try this,

Sum({<MonthName={"$(=Month(Max(Date)-1))"},Year=>}Act_Amt)

[#10]

Hi Max,

The new expression works for 2014 Jan now. but sadly...it doesn't work for 2013 Jan. Supposedly the amount 70 should be date for 2013 Dec. There should not be any data for prev. month of 2013 Jan. Please see snapshot below:

Really sorry to take up so much of your time...

[PrashantSangle]

Hi,

Then change your set analysis for previous month to

this,

Sum({<Date={"$(=Date(AddMonths(Max(Date),-1)))"},Year=>}Act_Amt)