Sumif / ifelse calculation

Hi QS community,

This is what my current table look like, I have 3 catagory, amazon studio, hvc and nonhvc. I want to combine amazon studio and nonhvc together as nonhvc, so only amazon studio and nonhvc under content value

my formula to get the most recent month ratio is use otp_num/otp_denom,

sum(ifelse(dateDiff(date2,${asofdate},‘MM’)=0,{otp_num},0)) / sum(ifelse(dateDiff(date2,${asofdate},‘MM’)=0,{otp_denom},0))

I want to get sum(otp_num) for amazon_studio+ non hvc, so that would be (47015+85742), same as otp_denom (48.72k+105.96k) , so i can get the correct otp ratio. what can I change in my formula?

can you change the content_value to do this?

ifelse(content_value=‘Amazon Studios’ OR content_value=‘nonhvc’,‘nonhvc’,‘HVC’)

Then you use this new category group as your group.

Is that what you want?

Hi MAX, if I use this formula, it will only give me hvc and non-hvc value, what i’m looking for is hvc and (nonhvc+amazon studio) together as ‘nonhvc’ , any suggestions?

So you still want to show in your content_value Amazon Studios and Nonhvc?

for instance you want


i don’t need amazon studio, just hvc and nonhvc, but the calculation is correct, nonhvc
will be 85742+47015(amazon studio)

Hello @Iris_Zhou - You need to create a new calculated field and use the same in your Pivot Table visualization.

ifelse({Content Value} = ‘Amazon Studios’, ‘Non HVC’, {Content Value} = ‘Non HVC’, ‘Non HVC’, ‘HVC’)

Please see the below snapshot for reference. Hope this helps! In case that helps to resolve your query, please mark this as Solution. Thank you!

1 Like