topic Re: How to Calculate the Last three Months Sales in QlikView

How to Calculate the Last three Months Sales

Tue, 23 Feb 2016 06:51:49 GMT

Could you help to tell me how to write the express to Calculate the Last three Months sales?

For example:

The 2016/1 sales amount is 100

The 2015/12 sales amount is 200

The 2015/11 sales amount is 300

The 2015/10 sales amount is 400

Select (2016 -1)

The Result:

Year Month Sales

2016 1 100+200+300

Select (2015 -12)

The Result:

Year Month Sales

2015 12 200+300+400

Re: How to Calculate the Last three Months Sales

Chanty4u — Tue, 23 Feb 2016 07:39:55 GMT

chk below

Calculating rolling n-period totals, averages or other aggregations

Re: How to Calculate the Last three Months Sales

tresB — Tue, 23 Feb 2016 08:36:10 GMT

The expression would depend on your date field data format. Try to share a qvw with sample data.

Re: How to Calculate the Last three Months Sales

Tue, 23 Feb 2016 08:57:49 GMT

Hi,

The following is my Excel Data.

Year	Month	Machine
2015	10	A
2015	10	B
2015	10	B
2015	10	B
2015	11	A
2015	11	A
2015	12	C
2015	12	C
2015	12	B
2015	12	B
2016	1	A
2016	1	A
2016	1	C
2016	2	B
2016	2	B
2016	2	B
2016	2	A

I need to calculate the Count(all Machine) per past three Month.

The rolling result shoud be : If I select "Year=2016, Month=1,"

Count[(Year =2016 ,Month=1 )+ (Year = 2015 ,Month=12) + (Year=2015 ,Month=11) Machine]

If I use the following exprssion, the value could be Zero. (I need to calculate the Value to cross the Year.)

rangecount(above(count(Machine), RowNo(Total)))

Re: How to Calculate the Last three Months Sales

jagan — Tue, 23 Feb 2016 09:03:29 GMT

Hi,

Check this link below if you have a date filed

Set Analysis for certain Point in Time

Set Analysis for Rolling Periods

Re: How to Calculate the Last three Months Sales

HirisH_V7 — Tue, 23 Feb 2016 09:23:20 GMT

Hi,

Check this as expression,

Sum({<Year=, Month =, Date={"$(='>=' & MonthStart(Today(), -2) & '<=' & MonthEnd(Today()))"}>} Sales)

HTH,

Hirish

Re: How to Calculate the Last three Months Sales

Anonymous — Tue, 23 Feb 2016 09:52:01 GMT

Try this expression

sum({<Date={$(=Max(Date))}>}SalesAmount)

+sum({<Date={$(=Max(Date)-1)}>}SalesAmount)

+sum({<Date={$(=Max(Date)-2)}>}SalesAmount)

Re: How to Calculate the Last three Months Sales

amit_saini — Tue, 23 Feb 2016 09:57:45 GMT

Hi Check:

https://community.qlik.com/message/701426#701426

set analysis to sum of sales of last 3 months.

Thanks,
AS

Re: How to Calculate the Last three Months Sales

smirkinaa — Tue, 23 Feb 2016 14:32:00 GMT

Hi. I prefer in similar situations to use precalculated in script values. But only when amount of months is constant.

In this situation i suggest to use additional table without association. We obtain tables after perfoming of script:

******************

Table1:

LOAD

MonthName(MonthEnd(MakeDate(ПериодГод,ПериодМесяц))) as YearAndMonth,

Продажи as Sales;

LOAD

Left(Период,4) as ПериодГод,

Mid(Период,Index(Период,'/') + 1) as ПериодМесяц,

Продажи

FROM

(ooxml, embedded labels, table is Лист1);

Table2:

LOAD

YearAndMonth as YearAndMonthForVisualisation

Resident

Table1;

********************

Table1:

YearAndMonth	Sales
jan 2016	100
dec 2015	200
aug 2015	260
nov 2015	300
sep 2015	350
oct 2015	400
jul 2015	500

Table2:

YearAndMonthForVisualisation

jan 2016

dec 2015

nov 2015

oct 2015

sep 2015

aug 2015

jul 2015

Values in YearAndMonth and in YearAndMonthForVisualisation are numeric in their nature. They are obtained by function 'MonthName'. After that we create visualisation: ListBox and Straight table. Dimension in straight table is 'YearAndMonthForVisualisation'.

We use expression:Sum ({<YearAndMonth={'>=$(#=MonthStart(AddMonths(Max(YearAndMonthForVisualisation),-2)))<=$(#=Max(YearAndMonthForVisualisation))'}>}Sales).

I hope it is helpful.

Re: How to Calculate the Last three Months Sales

Anonymous — Tue, 23 Feb 2016 15:48:48 GMT

Try this

Sum({<FieldDate={">=$(MonthStart(Addmonths(Min(Today()),-3)))<=$(MonthEnd(Addmonths(Max(Today()),3)))"}>}Sales)

Sum({<FieldDate={">=$(MonthStart(Addmonths(Min(FieldDate),-3)))<=$(MonthEnd(Addmonths(Max(FieldDate),3)))"}>}Sales)

Regards.

Re: How to Calculate the Last three Months Sales

Wed, 24 Feb 2016 06:52:33 GMT

Hi andrii smirkin,

I use your solution, it wroks fine now. Thanks a lot.