Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
Chanty4u
Esteemed Contributor III

Re: How to Calculate the Last three Months Sales

MVP
MVP

Re: How to Calculate the Last three Months Sales

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

Not applicable

Re: How to Calculate the Last three Months Sales

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

MVP & Luminary
MVP & Luminary

Re: How to Calculate the Last three Months Sales

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
Honored Contributor

Re: How to Calculate the Last three Months Sales

Hi,

Check this as expression,


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

HTH,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
vsudhakar
Contributor III

Re: How to Calculate the Last three Months Sales

Try this expression

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

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

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

amit_saini
Honored Contributor III

Re: How to Calculate the Last three Months Sales

Highlighted
smirkinaa
New Contributor III

Re: How to Calculate the Last three Months Sales

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.


maverjannet
Valued Contributor

Re: How to Calculate the Last three Months Sales

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.