I am using percentage difference to do a distinct count of a calculated column and at each category its working but the sub-totals and totals are showing blank in the Pivot table.
WoW for A is working find but for B, WoW is not working.
Hello @Priyanka_Bisht, welcome to the QuickSight Community!
How are you currently calculating your WoW values? Are you using periodOverPeriodPercentDifference? Also, what are you doing differently in your WoW B compared to WoW A. Sometimes the totals and subtotals can get a bit wonky on the pivot table when working with these aggregated fields. With a little more information, we can try to achieve your expected output!
Hello @DylanM ,
I am using percentDifference. Here are my calculations:
For A:
percentDifference
(
round({A},2),
[{week_number} ASC],
-1,
[{country_code},{sub_category}]
)
For B:
percentDifference
(
distinct_count({B}),
[{week_number} ASC],
-1,
[{country_code},{sub_category}]
)
Hello @Priyanka_Bisht, in the pivot table that you are applying this field to, are you grouping by the country_code and sub_category? If so, I don’t think you will also need the partition in your calculation. What you could try for the B calculation would be this:
periodOverPeriodPercentDifference(distinct_count({B}), {week_number}, WEEK, 1)
Another suggestion would be to use a week date field rather than a week number field. That might be causing some issue. You could utilize truncDate('WK', {date}) and the calculation might work better. I’ll link documentation to the aggregations I mentioned below:
Hello @DylanM , Unfortunately it didn’t work. I am also changing the Totals to Average for A but since distinct count is not available, I am not doing anything for B, it’s set to default. Kindly see screenshot.
Hello @Priyanka_Bisht, would you be able to add the distinct_count calculation into your custom SQL when you query the dataset so it can be a field value? Then you could handle it like a sum and utilize the average aggregation on the total. Otherwise, you could try it as it’s own calculated field and then use that in the periodOverPeriod calculation but I think it will run into similar aggregation issues. Importing it in the query will likely be the best course of action to get your desired result.
I will mark this as a solution for now, but if moving it to a dataset field doesn’t work or causes another issue please let me know!