Total Sales in previous month

[#1]

Hi Guys,to

I'm trying to make a pivot table with total sums of sales in previous month; the goal is to get something like this:

Year    Month_Name    Sales in Previous Month    Sales in This Month

2013    October    0.00    669,425.25

2013    November    669,425.25    632,081.48

2013    December    632,081.48    1,050,705.58

I've tried a solution found on iQlik (please see my attachment) but it doesn't work. Can anybody fix it and / or explain?

Thanks in advance!

Regards,
Pawel

[#2]

Pawel,

I have personal Edition, so I cannot open your application. But :

1) Set Analysis : they are computed ONCE per chart, not once per row (here, if you want to use set analysis, you would need different results across the lines => it is not good)

2) You can use functions like above() that will return the N lines above. The problem is for the 1st line where it returns 0.

If the months are across (in column), you will use Before() function instead.

3) You can also modify the model so that you create intermediate tables (called AsofTable) to simplify YTD, Previous period etc.

See a doc I have written on that topic: http://community.qlik.com/docs/DOC-4821

Fabrice

[pandiarajan]

Dear Pawel,

Kindly handle this one in Script level using Peek function.....

[ashwanin]

HI Pavel,

It can be possible by above function. Please go through the attached QVW . Hope it solve your query.

[pandiarajan]

Dear friend,

find the attachment. i hope it will solve ur problem

[#6]

Dear Ashwani,

Thank you for your help but I can't use the above function as client could select only one month - that would give only one line in a pivot. The above() function will be my last resort.

Kind reagrds,
Paweł

[#7]

Yes that is the problem of the above function

So you will need to update your model either by creating a new field populated with the peek() function in the script, or by creating an AsOfTable (if you want to do more than just returning the previous month)

Fabrice

[pandiarajan · Accepted solution]

user this script:

SalesDataTemp:

LOAD SalesDate,

     CustomerName,

     Qty,

     Value,

     Date(SalesDate,'MMM-YY') as SalesMonth,

     Date(SalesDate,'YYYYMM') as MonthNo

FROM

C:\Users\admin\Desktop\Sales.xlsx

(ooxml, embedded labels, table is Sheet1);

SalesData:

LOAD RowNo() as No,

CustomerName,

     MonthNo,

     SalesMonth,

     Qty as CurrentMonthQty,

     Value as CurrentMonthValue,

     if(RowNo() =1 ,0 ,Previous(Qty)) as PreviousMonthQty,

     if(RowNo()=1,0,Previous(Value)) as PreviousMonthValue

     Resident SalesDataTemp order by MonthNo;

     drop table SalesDataTemp;

[#9]

Dear Fabrice,

Thanks so much for your comments. For the time being, the only solution that I can use here is adding the month numbers to calendar (Oct 2013 would be 1, Oct 2014 would be 13 etc.) but I'm interested in any other ways to achieve that.

Kind regards,

Pawel

[#10]

...but it doesn't help, either. My expression is:

=sum({$<MonthNumber = {$(=Max(MonthNumber) – 1)}>} Sales)

why it gives zeroes every time?