Pivot table Calculation

Hello everyone, I need help to fulfill the below.

image

Users can filter the pivot table from the ‘Channel’.
Row ‘Total Channels’ will be sumation of the ‘Ratings’ field.
Row ‘Other’ will be ‘Total Channels’ - Ratings of selected channels (ex. A)

Please help me to achive this. thanks!

I would make a parameter called called Channel and check if it’s equal to the field {channel}.

Here is the calculated field.

ifelse(${Channel}={channel},{channel},'Other')

Use that as your rows and then sum the ratings as your value.

Finally, you can add a total to the bottom of your table.

1 Like

Thanks @Max for the reply.

As suggested I created a parameter as Channel and created cal field as follows.

ifelse(${Channel}={CHANNEL}, {CHANNEL}, ‘Other’)

However, the pivot table only shows the row ‘Other’ and does not show the channel wise ratings.

Would you be able to expalin this further. Thanks

Can you show me a screenshot of your selected ${Channel} is it A and what does it look like when you select A?

1 Like

Hi @Max, I used a sample table to illustrate my problem. Accutally in my database I have about 120 channels.

Below ‘New Channel’ = cal field
‘Share_w’ means ‘Ratings’

image

What is your parameter value? Is it linked to the channel field and have you selected a channel from your parameter?

1 Like

Hi Max, I just linked the channel name field to the parameter. Now I get the results when I select a single channel. But when I select multiple channels it doesn’t work. Can you guide me please?

Also, when I tried to change the parameter to string with multiple value, the cal field giving the below error.

It needs to be a single value parameter. It cannot be multi value.

1 Like

Hi Max, it worked when I use the below.

ifelse(in({CHANNEL_NAME}, ${Channel}), {CHANNEL_NAME}, ‘Other’)

However, when I select ‘Select All’ it only shows ‘Other’, but when I select multiple channels the report correctly comes. Any idea why?

Well when you select All it’s actually selects a reserved value. That reserved value is not in your list of channels.

1 Like

Thanks @Max, can you guide to fix this issue.

I believe you have to say isNull or whatever value you picked for your reserved value.

ifelse(in({CHANNEL_NAME}, ${Channel}) OR isNull(${Channel}), {CHANNEL_NAME}, ‘Other’)
1 Like