Solved: How to write an expression for below requirement - Qlik Community

kunkumnaveen · ‎2019-12-27

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

Kushal_Chawda · ‎2019-12-30

try below script

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

View solution in original post

Kushal_Chawda · ‎2019-12-30

this should work

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

View solution in original post

Taoufiq_Zarra · ‎2019-12-27

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

not clear ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

kunkumnaveen · ‎2019-12-27

Hi ,

I mean if a year 2019 and month Feb is selected from year and month filters.

What is the AVG of past three months is:

AVG of past three months are values of

2019 Jan

2018 Dec

2018 Nov

should bypass the year selection

Anil_Babu_Samineni · ‎2019-12-27

Perhaps this?

Avg({<Month={">=$(=AddYears(Max({<Year>}Month),-3))<=$(=Max({<Year>}Month))"}>} Sales)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

kunkumnaveen · ‎2019-12-29

Hi Anil,

I have tried your expression, but it not working ..something is missing in below bold set analysis

Avg({<Month={">=$(=AddYears(Max({<Year>}Month),-3))<=$(=Max({<Year>}Month))"}>} Sales)

kunkumnaveen · ‎2019-12-30

Hello All,

Any suggestion for the above requirement ...

Thanks

Anil_Babu_Samineni · ‎2019-12-30

My bad, Should be

Avg({<Month={">=$(=AddMonths~~AddYears~~(Max({<Year>}Month),-3))<=$(=Max({<Year>}Month))"}>} Sales)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

kunkumnaveen · ‎2019-12-30

I have done a change still its not working

Avg({<Month={">=$(=Date(AddMonths(Max({<Year>}Month),-3),'MMM')<=$(=Max({<Year>}Month))"}>}sales)

Taoufiq_Zarra · ‎2019-12-30

@Anil_Babu_Samineni's function works very well

=Avg({<Month={">=$(=AddMonths(Max({<Year>}Month),-3))<=$(=Max({<Year>}Month))"}>} Sales)

why are you adding Date() ?

if not can you share your Qlik file ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

kunkumnaveen · ‎2019-12-30

Hi,

Sorry to say but i am not allowed to share it,but i have attached a sample data file.