Limiting Date Dimension without limiting expression for Rolling 12 Month Rate

[michaelneck]

Hi All,

I am looking for a little help, I have created a rolling rate which I am using in a pivot table and I am using a month-year dimension to view the data from 2019 forward. In order to have 2019 forward correct I need the data for 2018 so that the rolling 12 month rate will calculate correctly from Jan-19 (data from Feb-18 to Jan-19). Is there a way that I can limit the dimension (to not show 2018) without limiting the expression. In my expression I am currently using the before function to calculate the rolling 12 months in the pivot. I have tried the following calculated dimension but the results did not match as expected.

Calculated Dimension Attempt:

=DATE(IF(PAYROLL_YEAR >= YEAR(AddYears(Today(),-1)),PAYROLL_MONTH_YEAR),'MMM-YYYY')

Expression Rolling 12 Months:

NUM(
RANGESUM(BEFORE(TOTAL SUM(
{<
PAYROLL_MONTH_NAME,PAYROLL_YEAR,
ESTATUS_CODE={1,3},
EGROUP_CODE={1,2}
>}
EVENT_COUNT),0,12))
*avg(1)*12

/

RANGESUM(BEFORE( TOTAL SUM({<
PAYROLL_MONTH_NAME,
PAYROLL_YEAR,
ESTATUS_CODE={1,3},

EGROUP_CODE={1,2}>}
HEADCOUNT),0,12))
*
avg(1)
, vformat_percentage_decimal)

Any help would be appreciated..

Thanks,

Michael

[sunny_talwar]

I wouldn't restrict it by using a calculated dimension, instead I would use set analysis to restrict my dimension.

[michaelneck]

Hi Sunny,

Thanks for your reply. Just a clarification, if I limit in set analysis would that not then remove the 2018 data altogether? So the rolling rates for 2019 would be incorrect and would ramp up until December 19 when 12 months would be present (Jan-19 to Dec 19) .

How would I go about it in set analysis without removing the 2018 data?

[sunny_talwar]

It is difficult to know what you are doing just by looking at an expression. I think it is doable using expression, but I would have to look at a sample to give you an exact answer which would work for you. Are you able to share a sample app?

[michaelneck]

Hi Sunny,

Unfortunately due to the sensitivity of the data I cannot upload a sample app, so I will try explain with the use of the below table image. So the third table below represents a rate comprised of data from the first two tables. It is a 12 month rolling rate but as the data starts in 2018 the rate is ramping up month on month in 2018 as more months are accumulated. So December 2018 is really the first month where the data is correct so the grey area below is essentially incorrect as there is not 12 months of data present (except Dec-18). My issue is that I would like to only show 2019 like in the 4th table below in a pivot chart using the dimension PAYROLL_MONTH_YEAR represented by month year below starting at Jan 2019 but having the 2018 data within the calculation to ensure the rate is correct. I am having issues limiting down the dimension to show only 2019 without having a knock on impact on the expression result. So I am wondering if there is any way either by using Calculated Dimension or Set Analysis within the expression to achieve this..

Many thanks for taking the time to have a look at it and happy to clarify further if needed..

[Brett_Bleess]

Have a look at the following Design Blog post, and not the other related post links at the bottom too, I think those may help you sort out the best way to approach things in your case.

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

Regards,
Brett