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,

I tried this:

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

it doesnt work. Anything wrong with the script?

[PrashantSangle]

Hi,

This time i am comparing the original date field instead of Monthname.

see my expression

[#4]

Hi Max,

Thanks a lot for your help. Really Appreciate it.

But I need to be able to display date when the month fields are selected. Linking directly to the date field will work but I cannot display date in the dashboard, only month.

Is there anyway I can link this to month? I tried inline, but when Jan is selected, it took the sum of 2013 & 2014 Jan data.

Any solution to this?

[stevedark]

Will this not only bring back a single day for the previous month?  Or is it the case that all dates are actually the first of the month?

You may want to take a look at the working examples I have uploaded for period to date and prior period comparison:

QlikView App: Set Analysis - Prior Period Comparison

QlikView App: Simple Year To Date Example (Set Analysis)

The other thing that you tend to need to do when fixing some fields in set analysis is ignore selections in all the others, eg:

Sum({<Date={'>=$(vPriorMonthStart)<=$(vPriorMTD)'},Year=,Month=,MonthName=>}Act_Amt)

Hope that helps a bit,

Steve

[#6]

Hi Steve,

Thanks for the reply. Yes, the case is that all dates are actually the first of the month. This is because there are actually no date field in the data and we need to create a dummy date for qlikview to work.

Please see attached file and data for your reference.

As i am running in a tight timeline on this, was desperately looking for a solution. Tried so many other solutions, there are still hiccups somehow along the way. Would be grateful if you could provide a direct solution to this. Thank you.

[stevedark · Accepted solution]

Hi Andy,

You look like you had got yourself in a bit of an over complicated situation there.  There was a significant bug in the INLINE load - causing the field names to be mangled.

All of the derivation of date parts is much more easily done in script, rather than joining inline tables anyway.  Also, you only need the one Date field as you can derive everything else from that.  The load script then simply becomes:

ExpenseData:

LOAD

     Date,

     Month(Date) as Month,

     Date(MonthStart(Date), 'MMM-YYYY') as [Month Year],

     Year(Date) as Year,

     Expenses,

     Act_Amt

FROM [.\Data2.xls]

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

You can add a whole lot of other Date derivations if you like (quarter etc.) you will find code for these on the Community forum - or in the examples I posted links to above.

For simple month to date comparison you need only two variables set:

set vMaxMonthYear = =Date(max([Month Year]), 'MMM-YYYY');

set vPriorMonthYear = =Date(addmonths(max([Month Year]), -1), 'MMM-YYYY');

And then the expressions for the current month and prior month columns are simply:

Sum({<[Month Year]={'$(vMaxMonthYear)'},Year=,Month=,Date=>}Act_Amt)

and

Sum({<[Month Year]={'$(vPriorMonthYear)'},Year=,Month=,Date=>}Act_Amt)

The short coming of this is that the current month will most likely only be month to date (as you can't look into the future) and the prior month will be a full month.  As all of your dates are in as day one then this is the best you can do anyway.  If you had days in the data you would also want to derive the day number, in the load:

Day(Date) as Day,

Find the day for the max month:

set vMaxDay = =Day(Max(Date));

And then include that in the expressions also:

Sum({<[Month Year]={'$(vPriorMonthYear)'},Day={'<=$(vMaxDay)'},Year=,Month=,Date=>}Act_Amt)

The only thing you would need to worry about there is months with fewer days, ie. on the 28th of Feb you would probably want to compare with the 31st of Jan - not the 28th.

You then also get into seasonality and number of weekends in any given month - and things start getting a lot more complicated.  I digress.

Hopefully the load script amendments, variables and expressions at the top of this post will solve your immediate issues.

I've attached a working example as well.

Regards,

Steve

[#8]

Hi Steve,

Superb! It works very well! It look so much less complicated as compared to the other solutions.

I find that defining the date parts are very useful as well. Thank you very much for the detailed explanations.

Thanks!

Andy

[stevedark]

Glad it's working for you now.

[#10]

Hi Steve,

Thanks for the Solution.. i was as well Looking for the same .. I got me this Qvw..

i as well Saw your Videos and Other Qvw... Can u tell any Place where i can get ur All Video and Qvw to

Learn More about Qlikview

[#11]

As well can u tell me if i want to compare Quarterly as well .. So what is nee to do ...