Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
aaronnayan
Contributor III

This month 12 months ago

HI Guys,

Hoping you could help me.

I want to avg(sales) for the current month 12 months ago so July 2017

I already have a current month flag field which returns 1 or 0,  (today's month- July 2018.)

I want to get the avg(sales) for the current month, 12 months ago.

I have already tried this but its not working:

AVG({<MonthYear={"$(=Monthname(addmonths(Max(MonthYear),-12)))"}>}Sales)

Could i please request to try do this in the front end if possible

Thanks,

Aaron

2 Replies
MVP & Luminary
MVP & Luminary

Re: This month 12 months ago

How is the MonthYear field created? It should be a date, but not just a string value. Otherwise max(MonthYear) won't return a date value that can be calculated with in the addmonths function.

You could check if Num(MonthYear) returns a number.


talk is cheap, supply exceeds demand
aaronnayan
Contributor III

Re: This month 12 months ago

Hi Gysbert

I have another Date field that I am using called DateKEY. This can be turned into a Number with the NUM()

I am able to get July 2017 with this expression

Monthname(addmonths(Max([DateKEY),-12))

However i cant get the average of monthly sales in there

What would you propose?

Thanks,

Aaron