How to restrict the period in a line chart

[BoXiangWang]

Hi all,

I know that set analysis can be used to restrict the period of data in a chart.

However, I don't know how to restrict the period in a running total chart.

I am creating a line chart for turnover and I want to show the turnover rate = # turnover per month / L12 months avg # headcount per month

Here is the expression I used to calculate the headcount: =(RangeSum(Above(Count(distinct {<%headcount_key={"*"}, is_seconded={"0"} >} dim_ppl_employee_sk), 0, 12))/12)

I want to know how to restrict the result only after 2022-01-01 since my data starts from 2021-01-01 so the valid average headcount is supposed from 2022.

I tried to use set analysis {=$(=AddMonths(Min(date_month),12))"}>} but it only restricts the raw data, so the running total starts from 2022. But this is not I want. I want the average result after 2022.

Is there any idea? Thanks.

[Sayed_Mannan]

Hi, You can use an if condition in your expression to restrict the calculation to only consider dates after 2022-01-01. Here's how you can modify your expression:

=if(date_month >= MakeDate(2022, 01, 01), RangeSum(Above(Count(distinct {<%headcount_key={"*"}, is_seconded={"0"} >} dim_ppl_employee_sk), 0, 12))/12, null())

In this expression, date_month should be the field that contains the date in your data. MakeDate(2022, 01, 01) creates a date value for 2022-01-01. The if condition checks if date_month is greater than or equal to this date. If it is, it calculates the running total as before. If not, it returns null(), effectively ignoring these dates in the chart.

Please replace date_month with your actual date field name. This should give you the average result only after 2022.

Remember to adjust the date format in MakeDate()  function if your date format is different.

I hope this helps! Let me know if you have any other questions.