I have a dataset which has two columns Booked Channel & Reported Channel, Pax Count for my analysis. I want to create a pivot table visualization as follows.
The booked Channel and Reported Channel could be different in the same row. I want to generalize the Channel for grouping purpose. I would like to define Channel as a static list using a calculated field and then count the Pax counts as Booked and Reported based on their values in the dataset.
I am sure there are situations you have dealt with this like this. I would appreciate your feedback
You said you have two columns in the first sentence and later on said that booked channel and reported channel can be different in the same row. So, I’m a bit confused here.
Please create a sample in Arena to allow other community members to take a shot at helping you.
Hi @Giridhar.Prabhu,
Transforming the Booked Channel field into a new field that only shows individual channels isn’t exactly possible but a work-around you could use is to setup a denseRank calculation, partitioned by channel, and filter down to 1 row per channel type (just rank 1s).
Then you could use LAC-W calculations, like sumOver, to retrieve your values for the Booked and Reported fields. Once again partitioning those fields by the booked channel field to ensure they are grouped appropriately.
