Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

michael_anywar
Contributor

Average field data basing 3 months Timestamp field

Hello, am trying to calculate the average Qty from the previous 3 months, but it not working.

Below are the expressions i tried out. I want use as Measures for visualisation.

However my Date field is Timestamp.  Format: DD-MM-YYY hh:mm

I even used variables;

=Sum({<date={'>=1/12/2017 < = 1/3/2018'}>}Qty)

I even tried using created variables:

vLast3MonthStartDate= MonthStart(Today(),-4 )

vLastMonthEnd= MonthEnd(Today(),-1 )

vSumLast3MonthQuantitySold = Sum({$<date={">=$(vLast3MonthStartDate)<$(vLastMonthEndDate)"}>}Qty)

vQuartyAveragequantity = ((Sum $(vSumLast3MonthQuantitySold))/3)

1 Solution

Accepted Solutions
sureshqv
Esteemed Contributor III

Re: Average field data basing 3 months Timestamp field

or

sum({$<  [Month Num],date={">=$(=AddMonths(Max(date), -3))<=$(=Max(date))"}>} Qty)

4 Replies
sureshqv
Esteemed Contributor III

Re: Average field data basing 3 months Timestamp field

is this ?

Expression: =RangeAvg(Below(Sum(amount), 0, 3))

sureshqv
Esteemed Contributor III

Re: Average field data basing 3 months Timestamp field

or

sum({$<  [Month Num],date={">=$(=AddMonths(Max(date), -3))<=$(=Max(date))"}>} Qty)

michael_anywar
Contributor

Re: Average field data basing 3 months Timestamp field

Thanks sureshqv‌ This worked. Managed to populate the field.

But does this mean that -3 caters for the month portion of the date..?

For instance if I ever wanted the previous 5 months i'd just say sum({$<  [Month Num],date={">=$(=AddMonths(Max(date), -5))<=$(=Max(date))"}>} Qty)


And what if I needed data to include the current month inlcuded?

Should I assume that will be by removing the negative  from the month number?As in  sum({$<  [Month Num],date={">=$(=AddMonths(Max(date), 4))<=$(=Max(date))"}>} Qty)

Thanks though. I know am a lot now..

sureshqv
Esteemed Contributor III

Re: Average field data basing 3 months Timestamp field

just look into    addmonths concept in help file you will get to know more.

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/DateAndTimeFun...

Community Browser