Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
I have break the expression and took in two kpi,
can you look at the QLik file attached
Avg in blue
Hi,could you plz post me that expression what you have used in text box ,as i am using qliksense ....
=Avg({<Month={">=$(=AddMonths(Max({<Year>}Month),-3))<=$(=Max({<Year>}Month))"}>} [sum(Count_Br)])
try below script
avg({<Year,M_Month, Date = {">=$(=monthstart(addmonths(max(Date),-3)))<=$(=monthend(addmonths(max(Date),-1)))"}>}Count_Br)
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
Did you tried my solution?
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)
Please find attached sample data
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