topic Re: Comparing Current Month and Previous Month Sales in QlikView

Comparing Current Month and Previous Month Sales

Sun, 02 Mar 2014 02:45:04 GMT

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.

Re: Comparing Current Month and Previous Month Sales

nilesh_gangurde — Sun, 02 Mar 2014 05:49:22 GMT

Hi,

The above expressions are correct. just check the format of date Field. Ideally it should be in Number.

Try using below expression.

LOAD Year,

Month,

Product,

Sales,

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

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

FROM

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

Current Month Expression:

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

Previous Month Expression:

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

-Nilesh

Re: Comparing Current Month and Previous Month Sales

SunilChauhan — Sun, 02 Mar 2014 06:52:56 GMT

See the attached file.

You can apply this in ur application

hope this helps

Re: Comparing Current Month and Previous Month Sales

Sun, 02 Mar 2014 10:39:51 GMT

Hi Nilesh,

Thank you for your reply.

The date format is in number but it still doesn't work.

But thanks for helping out, really appreciate it.

Re: Comparing Current Month and Previous Month Sales

Sun, 02 Mar 2014 10:41:57 GMT

Hi Sunil,

Thank you very much for taking your time to create a dummy file. It is very helpful.

It works now.

Thank you!

Re: Re: Comparing Current Month and Previous Month Sales

Sun, 02 Mar 2014 11:33:56 GMT

Hi Sunil,

If I want to display the months from Jan-Dec, without Duplicate. How should I go about doing it?

Currently, it will display as follow:

Attached a working file and data file for your reference:

Re: Comparing Current Month and Previous Month Sales

PrashantSangle — Sun, 02 Mar 2014 14:11:38 GMT

Hi,

You can separate your month field from your date.

use Month(yourdatefield) as Month

Re: Re: Re: Comparing Current Month and Previous Month Sales

SunilChauhan — Sun, 02 Mar 2014 14:16:44 GMT

see the attached file

I THING Requirement chart is what you looking for.

hope this helps

Re: Comparing Current Month and Previous Month Sales

Sun, 02 Mar 2014 14:42:47 GMT

Hi max,

Could you show an example using my working file attached?

Thank you!

Re: Re: Re: Re: Comparing Current Month and Previous Month Sales

Sun, 02 Mar 2014 14:51:09 GMT

Hi Sunil,

No, i wasn't looking for a requirement chart. In fact i need to create a dashboard, so the Month field ideally should not be an extensive list.

I've tried this as well, it display the months field properly but there are 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.

Maybe you have a solution to the one above?

Re: Comparing Current Month and Previous Month Sales

SunilChauhan — Sun, 02 Mar 2014 15:02:02 GMT

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

Re: Comparing Current Month and Previous Month Sales

PrashantSangle — Sun, 02 Mar 2014 15:04:26 GMT

Hi,

I am using Qlikview 10 Personal edition .

If possible can you post your script.

Thanks and Regards,

max

Re: Re: Comparing Current Month and Previous Month Sales

Sun, 02 Mar 2014 15:15:28 GMT

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

Re: Re: Comparing Current Month and Previous Month Sales

Sun, 02 Mar 2014 15:18:45 GMT

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?

Re: Comparing Current Month and Previous Month Sales

PrashantSangle — Sun, 02 Mar 2014 15:20:48 GMT

Hi,

In your png you are missing ;

Re: Comparing Current Month and Previous Month Sales

PrashantSangle — Sun, 02 Mar 2014 15:23:47 GMT

Hi,

Month(Date) as MonthName

Re: Comparing Current Month and Previous Month Sales

Sun, 02 Mar 2014 15:26:02 GMT

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?

Re: Comparing Current Month and Previous Month Sales

PrashantSangle — Sun, 02 Mar 2014 15:30:44 GMT

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

Re: Comparing Current Month and Previous Month Sales

Sun, 02 Mar 2014 15:32:02 GMT

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?

Re: Comparing Current Month and Previous Month Sales

PrashantSangle — Sun, 02 Mar 2014 15:39:19 GMT

Hi,

try this

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