topic Re: Total Sales in previous month in QlikView

Total Sales in previous month

Mon, 24 Feb 2014 09:33:37 GMT

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

Re: Total Sales in previous month

Mon, 24 Feb 2014 09:50:08 GMT

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

Re: Total Sales in previous month

pandiarajan — Mon, 24 Feb 2014 09:52:12 GMT

Dear Pawel,

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

Re: Total Sales in previous month

ashwanin — Mon, 24 Feb 2014 10:04:33 GMT

HI Pavel,

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

Re: Total Sales in previous month

pandiarajan — Mon, 24 Feb 2014 10:20:36 GMT

Dear friend,

find the attachment. i hope it will solve ur problem

Re: Total Sales in previous month

Mon, 24 Feb 2014 10:21:47 GMT

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ł

Re: Total Sales in previous month

Mon, 24 Feb 2014 10:47:31 GMT

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

Re: Total Sales in previous month

pandiarajan — Mon, 24 Feb 2014 10:51:34 GMT

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;

Re: Total Sales in previous month

Mon, 24 Feb 2014 11:12:23 GMT

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

Re: Total Sales in previous month

Mon, 24 Feb 2014 11:30:02 GMT

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

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

why it gives zeroes every time?

Re: Total Sales in previous month

Anonymous — Mon, 24 Feb 2014 11:34:01 GMT

Hi Pawel,

I have done this . u can check the qvw file in which both current month and previous month wise sales is working .

If u select the year then it will show the current month and previous month sales of selected year according to the product code.

Re: Total Sales in previous month

Mon, 24 Feb 2014 12:19:18 GMT

Dear Dhiraj,

Thank you very much and I really appreciate your help. It don't seem to work when I add month name to dimensions - can you tell me why?

Re: Total Sales in previous month

Anonymous — Mon, 24 Feb 2014 12:36:00 GMT

Hi Pawel,

The month_name would be work as dimention when u will use the bar chart. Bcz when u will pass the month_name as a dimension in pivot table then it will show only that month sales which is in dimension .

According to that month which is in dimension the sales would be appear if we will calculate the previous month then it will show the zero bcz it will not get the dimension.