Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Total Sales in previous month

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

1 Solution

Accepted Solutions
pandiarajan
Contributor

Re: Total Sales in previous month

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;

12 Replies
Not applicable

Re: Total Sales in previous month

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
Contributor

Re: Total Sales in previous month

Dear Pawel,

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

ashwanin
Valued Contributor

Re: Total Sales in previous month

HI Pavel,

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

pandiarajan
Contributor

Re: Total Sales in previous month

Dear friend,

find the attachment. i hope it will solve ur problem

Not applicable

Re: Total Sales in previous month

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ł

Not applicable

Re: Total Sales in previous month

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
Contributor

Re: Total Sales in previous month

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;

Not applicable

Re: Total Sales in previous month

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

Not applicable

Re: Total Sales in previous month

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

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

why it gives zeroes every time?

Community Browser