Create a column in straight table

kunkumnaveen — Thu, 26 Dec 2019 06:16:47 GMT

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

Re: Create a column in straight table

rubenmarin — Thu, 26 Dec 2019 07:43:48 GMT

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

topic Re: Create a column in straight table in QlikView

Create a column in straight table

Re: Create a column in straight table