Aggregated field not allowed in the dimension

Question & Answer
,
1

Hi Team,

I have calculated filed like this below, Now I need to create a pivot table and count all the rows with 10,20,30, etc…Pivot visual is complaining that custom aggregate field is not allowed. Appreciate if someone could help me accomplish it.

ifelse(
{percent_item} > 1 AND {percent_item} < 10,‘10’,
{percent_item} > 11 AND {percent_item} < 20,‘20’,
{percent_item} > 21 AND {percent_item} < 30,‘30’,
{percent_item} > 31 AND {percent_item} < 40,‘40’,
{percent_item} > 41 AND {percent_item} < 50,‘50’,
{percent_item} > 51 AND {percent_item} < 60,‘60’,
{percent_item} > 61 AND {percent_item} < 70,‘70’,
{percent_item} > 71 AND {percent_item} < 80,‘80’,
{percent_item} > 81 AND {percent_item} < 90,‘90’,
{percent_item} > 91 AND {percent_item} < 100,‘100’,
“0”
)

2

Hi @EJRD,

Can you share your calculated field for percent_item?

3

it should be

  • sum(count_item) / sum(count_item1)+ sum(count_item) *100

My end goal is like this

image
image258×249 3.6 KB

But I am getting this error in pivot visual when I try to add the calc field

image

4

Try replacing all the sums in your calculated field with sumOver (I’m assuming that count_item and count_item1 are not calculated fields).

Example:

sumOver(count_item, [{product}], PRE_AGG) / sumOver(count_item1, [{product}], PRE_AGG) + sumOver(count_item, [{product}], PRE_AGG) * 100

I’m using {product} in my example but you should replace it with the dimension which is in your dataset.

2 Likes
5

Hi @EJRD,
It’s been awhile since we last heard from you, did you have any additional questions regarding your post or did the solution provided above help with your case?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

6

Hi @EJRD,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information if needed.

Thank you!

7

Sorry for late response. The issue is now resolved.