How to write an expression for below requirement

[kunkumnaveen]

Hello All,

I need to design a bar graph with Dimension (B_Mode)and with a expression which accumulate last three months  avg(Count_Br ) excluding the selected month 

For Example :i have max data till 2019 Oct.

if no month and year is selected it has to  then take the avg of past three months(Sep,Avg,Jul)

if any month is selected (2019,Feb) then it should take avg of  the past three months(2019Jan,2018Dec,2018,Nov)..

Chart:Bar Chart

Dimension: B_Mode

Expression: Avg({   ?   }Count_Br)

Thank

Please find the attached sample data...

[kunkumnaveen]

I have break the expression and took in two kpi,

[Taoufiq_Zarra]

can you look at the QLik file attached

Avg in blue

[kunkumnaveen]

Hi,could you plz post me that expression what you have used in text box ,as i am using qliksense ....

[Taoufiq_Zarra]

=Avg({<Month={">=$(=AddMonths(Max({<Year>}Month),-3))<=$(=Max({<Year>}Month))"}>} [sum(Count_Br)])

[Kushal_Chawda · Accepted solution]

try below script

avg({<Year,M_Month, Date = {">=$(=monthstart(addmonths(max(Date),-3)))<=$(=monthend(addmonths(max(Date),-1)))"}>}Count_Br)

[kunkumnaveen]

Hi,i think i didn't explained my requirement clear. In a chart i need to show avg of each B_mode (Avg means when no year or month is selected it has to take that particular B_mode past three months avg excluding max date)

i mean in below screen for B_mode =PER,the max date is 27/10/2019 so avg for Per is Avg (27/09/2019+27/08/2019+27/07/2019) which is 341

Your expression,what i am getting is entire yr avg when no year and month is selected 

[Kushal_Chawda]

Did you tried my solution?

[kunkumnaveen]

Hi,Thanks for your reply ,Yes i tried below exp

avg({<Year,M_Month, [Date(Date)] ={">=$(=monthstart(addmonths(max([Date(Date)]),-3)))<=$(=monthend(addmonths(max([Date(Date)]),-1)))"}>}
[sum(Count_Br)])

But i am getting same output instead of Max three months(sep,Avg,Jul)

[kunkumnaveen]

Please find attached sample data

[Kushal_Chawda]

check my reply again. you are not using same expression,

don't use date(date) in set analysis, just use date field name

don't use sum(coumt_br), just use count_br