I have a pivot table with 3 levels of grouping: continent, country, and city. I want the field to be shown at the city level as date, but at the country and continent levels I want it to display a count instead. Is there a way to do this?
The column is a timestamp.
I see
and for totals: (here I would like to select count)
Hello @igfasouza, if the options are not present within the field well like you are showing above, then there is not a way to make this happen. The only possible option would be to allow the user to select between the max date or count of dates, but that would show them all the same for city, country, and continent.
You could build a parameter with 2 options for the user like Count of Dates or Timestamp. Then you could create a calculated field like this:
ifelse(${Parameter} = "Count of Dates", countOver({Date}, [{Country}], PRE_AGG),
maxOver({Date}, [{City}], PRE_AGG))
That would at least allow you to display the data you want, but it won’t display it in the visual like you are expecting.
thanks for the answer.
I did using 2 columns
like: in city level the first columns show the count, that will be always zero or 1, so a change to yes/no and the second columns shows the date
and in continent or country level the first column show the count, the second I put hide.
but is a shame that I can’t merge those 2 columns in one.
Hello @igfasouza, thank you for following up! I am glad you were able to find a work-around. I wish we could’ve found a way to make this work as you expected, but the pivot tables get tricky and implementing logic to show 2 different aggregations is super limited.