1

I have data look like this.

enter image description here

I want to create line chart that show year_number on x-axis and show kpi_value on y-axis, but there're complex condition that I want to show kpi_value only from max month of each year, if you see on the data in year 2022 there're only 2nd month data recorded so the data point that might show on the line chart should be [(2020, 19), (2021, 44.2), (2022, 44.9)]

Surat Suntong
  • 31
  • 1
  • One way is to treat 0 as NULL and it won't show up in graph. Might need to adjust it in "Special Values (eg. Null) dropdown. – Wizhi Jul 25 '23 at 19:15
  • Actually, I treat 0 as NULL then how to only bring kpi_value from max month in each year? – Surat Suntong Jul 25 '23 at 20:02

1 Answers1

0

Here is one approach using LOD calcs.

Create an LOD calc to compute the latest month with reporting for each year, called, say Final_Month, defined as

{ FIXED [year_number] : MAX(IF ZN([kpi_value]) > 0 THEN [month] END) }

And then define a second calc called, say Final_Month_KPI, defined as

IF [month] = [Final_Month] THEN [kpi_value] END

This calc will have the kpi_value for the last month with reporting each year and will be null otherwise. You can then display the final month kpi field as a measure or attribute, since the null values will be ignored.

Alex Blakemore
  • 11,301
  • 2
  • 26
  • 49