Sumif / ifelse calculation

Iris_Zhou · December 5, 2022, 9:52pm

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?

Max · December 5, 2022, 10:23pm

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?

Iris_Zhou · December 5, 2022, 10:29pm

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?

Max · December 5, 2022, 10:51pm

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

for instance you want

Iris_Zhou · December 6, 2022, 2:37am

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

sagmukhe · December 6, 2022, 8:52am

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!