Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Hi,
I am using Qlikview 10 Personal edition .
If possible can you post your script.
Thanks and Regards,
max
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).
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?
Hi,
In your png you are missing ;
Hi,
do
Month(Date) as MonthName
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?
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
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?
Hi,
try this
Sum({<MonthName={"$(=Month(Max(Date)-1)),Year={"$(=(Year))"}>}Act_Amt)"}>})