Create a column in straight table

[kunkumnaveen]

Hello All,

Need help in creating a calculated column(Last 6 months Mat_Count) in straight table.

In the  below table ,beside Sum(Mat_count) column, i have to create a column - sum of 6 months mat_count  of each month

 For example for 2019 and Oct  month the sum of mat 6 months is as shown below

 

This above 76224  value should be shown in Last 6 months column beside sum(mat_Count) in last row  where year 2019,Oct.

the same for the Sep month etc...

What is try :

Took Two variables :

1)vFloor_Max=max(Floor_Date)

2)vMaxDate_5= num(addmonths(max(Floor_Date),-6))

Expression:sum({<Floor_Date={">=$(=vMaxDate_5)<=$(=vFloor_Max)"}>}mat_count)

the expression is working when no month (Jan,Feb etc...) has selected ,when a month has selected it showing only selected month sum(mat_count) insteady of showing sum of six month mat_count from the selected month.

Note: Six months should be calculated irrespective of years I mean if 2019 feb is selectes then six months from that

2019 Feb, 2019Jan,2018Dec,2018Nov,2018Oct,2018Sep,2018Aug..

Please find the sample data attachment 

Thanks

[rubenmarin · Accepted solution]

Hi, I think you will need help from script, precalculating values, having additional fields for last months values or using an asof table: https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130