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.

[SunilChauhan]

why not take

Month(Date) as Month in script;

or take inline

Load * inline

Month,MonthName

1,Jan

2,Feb

3,Mar

4,Apr

5,May

6,Jun

7,Jul

8,Aug

9,Sep

10,Oct

11,Nov

12,Dec

];

and use MonthName

hope this helps

[PrashantSangle]

Hi,

I am using Qlikview 10 Personal edition .

If possible can you post your script.

Thanks and Regards,

max

[#4]

Hi Sunil,

Thanks for the quick reply.

I've tried using month(date) as month, but it doesnt seems to load properly.

LOAD Year,

    Date,

   month(Date) as Month,

    Expenses,

    Act_Amt,

The inline script looks good. But i tried using your script but it does not work. i'm having this error (snapshot attached).

[#5]

Here you go.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

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

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

SET DateFormat='D/M/YYYY';

SET TimestampFormat='D/M/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,

     Date,

     Month,

     Expenses,

     Act_Amt

FROM

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

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

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

So far this method works best for me but only having 2 issues:

1. I need to display the month number in a text month format, but when i tried to change it to the month format, data does not display properly.

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

Would you be able to help?

[PrashantSangle]

Hi,

In your png you are missing ;

[PrashantSangle]

Hi,

do

Month(Date) as MonthName

[#8]

Hi Max,

I did input ; at the end but the error msg did not show. Even with the ; the script still doesnt work..

any idea?

[PrashantSangle]

Hi,

try this to create

load * inline

statement through

Insert->Load Statement->Load Inline

and also check there is any syntax error before inline load

[#10]

Hi Max,

Thanks for your help.

I applied the script but the previous month script does not work now...

I've edited the previous month expression to:

sum({<MonthName={"$(=(MonthName)-1)"},Year={"$(=(Year))"}>}Act_Amt)

Is this correct?

[PrashantSangle]

Hi,

try this

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