Disable pivot table's date aggregation?

Question & Answer
, , ,
1

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.

image

1 Like
2

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 =

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

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!

3

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?

4

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:

ifelse(
    dateDiff(
        {Order Date},
        addDateTime(6,'MM',{Order Date}),
        "YYYY"
    ) = 1,
    "H2",
    "H1"
)

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

Regards,
Andrew

1 Like
5

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!