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"