How to Calculate the Last three Months Sales

[#1]

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

[Chanty4u]

chk below

Calculating rolling n-period totals, averages or other aggregations

[tresesco]

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

[#4]

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)))

[jagan]

Hi,

Check this link below if you have a date filed

Set Analysis for certain Point in Time

Set Analysis for Rolling Periods

[HirisH_V7]

Hi,

Check this as expression,

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

HTH,

Hirish

[Anonymous]

Try this expression

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

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

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

[amit_saini]

Hi Check:

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

set analysis to sum of sales of last 3 months.

Thanks,
AS

[smirkinaa]

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.

[Anonymous]

Try this

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

or

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

Regards.