Disable pivot table's date aggregation?

I have ‘Sale Date’ column, and ‘Period’ calculated based on ‘Sale Date’ in a pivot table for a dashboard. We want to have data ranging multiple years aggregated together, but the widest interval Sale Date aggregate is Annual. So looking for a way to disable Sale Date aggregation, and aggregate based on a calculated field - Period, so we can bypass issue resulting from Sale Date aggregated Annually.


1 Like

Hello @sunnysky230, if you want to change the way Sales Date is returning based on a selection made for period as a parameter controlled filter, you can utilize an ifelse statement in a calculated field instead of the Sales date field directly. It would look something like this:
Sale Date by Period =

${Period} = 'Annually', truncDate('YYYY', {Sale Date}),
${Period} = 'Quarterly', truncDate('Q', {Sale Date}),
${Period} = 'Monthly', truncDate('MM', {Sale Date}),

You could implement similar functionality in your Period column that you made with a calculated field to make sure both match the value from your parameter controlled filter. You can manually add value options in a dropdown and utilize those to determine how your Sale Date value will be returned in the pivot table. I hope this helps!

Hi @DylanM, In this case, wouldn’t the new ‘sale_date_by_period’ still reference to {Sale Date}, hence we still need {Sale Date} in the pivot table column, which force us to still break data down annually?

Hi @sunnysky230,

I’ve built a very simple analysis that would allow you to aggregate based on which half of the year sales would fall into. Does something like the following help?

Create a calculated field Sale Date By Half-Annual as follows:

        {Order Date},
        addDateTime(6,'MM',{Order Date}),
    ) = 1,

Then bring that into an analysis with a pivot table and a sales measure as follows:


1 Like

Hello @sunnysky230, did the response from @abacon help guide you towards your expected output? I will mark his reply as the solution, but let us know if you have any follow-up questions. Thank you!