View the original community article here
Last tested: Nov 30, 2020
Goal
When we use a pivot table, sometimes we want to display ONLY most recent date/week/year column
Challenge
But Number of pivoted columns are changing based on filter definitions. So we don't want to hard code to define reference column with pivot_index function. How can we dynamically change referenced pivot column based on latest date shown in the data tab?
e.g.
When I run query without filter(1), I have 5 pivoted columns (year 2013, 2014, 2015, 2016 and 2017). In this case, we want to display user counts only for year 2017. However, if end-user filter out data(2) with the city name River Falls, we see 3 pivoted column in table chart. In this case, we want to display user counts for year 2016.
(1) Without Filter value:
(2) With filter value:
Solution
pivot_where(
${users.created_year}
= pivot_index(
${users.created_year}
,max(
pivot_row(
pivot_column()
)
)
)
, ${users.count}
)
Here is a template!
pivot_where(
${PUT YOUR TIME/DATE/WEEK/YEAR FILED} # I used YEAR for this sample
= pivot_index(
${PUT YOUR TIME/DATE/WEEK/YEAR FILED} # I used YEAR for this sample
,max(
pivot_row(
pivot_column()
)))
, ${PUT YOUR MEASURE FIELD}
)
If you want to display only this table calc field, you can hide unnecessary field by clicking "Hide from Visualization"
This content is subject to limited support.