Hello everyone, I need help to fulfill the below.
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!
Max
June 2, 2023, 2:27pm
2
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
Max
June 2, 2023, 4:19pm
4
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’
Max
June 2, 2023, 6:15pm
6
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.
Max
June 3, 2023, 1:29pm
9
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?
Max
June 3, 2023, 2:36pm
11
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.
Max
June 4, 2023, 3:17pm
13
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