Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
michaelneck
Contributor III
Contributor III

Limiting Date Dimension without limiting expression for Rolling 12 Month Rate

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

5 Replies
sunny_talwar

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

michaelneck
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

Qlik Issue.png

Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.