Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to Calculate the Last three Months Sales

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

10 Replies
tresesco
MVP
MVP

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

Not applicable
Author

Hi,

The following is my Excel Data.

   

YearMonthMachine
201510A
201510B
201510B
201510B
201511A
201511A
201512C
201512C
201512B
201512B
20161A
20161A
20161C
20162B
20162B
20162B
20162

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
Luminary Alumni
Luminary Alumni

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

Hi,

Check this as expression,


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

HTH,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
Anonymous
Not applicable
Author

Try this expression

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

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

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

smirkinaa
Contributor III
Contributor III

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:

YearAndMonthSales
jan 2016100
dec 2015200
aug 2015260
nov 2015300
sep 2015350
oct 2015400
jul 2015500

Table2:

YearAndMonthForVisualisation
jan 2016
dec 2015
nov 2015
oct 2015
sep 2015
aug 2015
jul 2015


export.png


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'.

1.png

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


I hope it is helpful.


Anonymous
Not applicable
Author

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.