Case Statement with calculation

WizardOz

I wanted to create a calculated field using case statement, but have a trouble to get what I want:

I have a dataset looks like below, in this new calculated field,

if Category is A( and many other categories) and Date is in last 7 days then SUM(Value)

if Category is B or C (only these 2 categories) and Date is in last 7 days, then AVG(Value)

Date Category Value

10/11/2018 D 115

10/19/2018 A 15

10/15/2018 A 25

10/21/2018 B 11

10/21/2018 A 16

10/21/2018 B 3

10/23/2018 A 27

10/24/2018 B 5

10/24/2018 C 1

Thank you.

Olivia

Find more posts tagged with

Comments

NewsomSolutions

Olivia - what about your result is not what you want? Error? Data being aggregated incorrectly? Incorrect dates being included? Do you have the real Case statement you could include? - matt

WizardOz

Hi, Matt,

This is what I wrote , but I did not get any output.

Can you please help me -- What is missing here?

Thank you very much.

Olivia

CASE
WHEN (SUM(CASE
WHEN `date`> (CURDATE()-7) and `Category`<>'Avg Watch Time (Sec)' and `Category`<>'Avg Watch Percentage (%)' THEN `Total`
END)) = 0 THEN 0
ELSE(AVG(CASE
WHEN `date`> (CURDATE()-7) and `Category`='Avg Watch Time (Sec)' and `Category`='Avg Watch Percentage (%)' THEN `Total`
END))

END

WizardOz

Hi, Matt, I rewrote the statment like this, but still nothingcme out .

Can you help?

Olivia

(case
when `date`> (CURDATE()-7)
then SUM(case when `Category` IN ('Views','Likes', 'Dislikes', 'Comments', 'Shares', 'Subscribers') then `Total` END)

when `date`> (CURDATE()-7)
then AVG(case when `Category` IN ('Avg Watch Time (Sec)', 'Avg Watch Percentage (%)') then `Total` END)
end)

NewsomSolutions

Try this...

(case

when `date`> CURDATE()-7 and `Category` IN ('Views','Likes', 'Dislikes', 'Comments', 'Shares', 'Subscribers')
then sum(`Total`)

when`date`> CURDATE()-7 and `Category` IN ('Avg Watch Time (Sec)', 'Avg Watch Percentage (%)')
then avg(`Total`)
end)

WizardOz

Thanks, Matt. But nothing come out.

NewsomSolutions

@WizardOz are you checking my syntax? I've made some edits below to describe what the query is doing so you can edit where needed for you.

(case

when `date`> CURDATE()-7 and [This it the field you are searching] IN ('string1','string 2', 'string 3', 'Comments', 'Shares', 'Subscribers')

then sum(`Total`)

when`date`> CURDATE()-7 and `Category` IN ('Avg Watch Time (Sec)', 'Avg Watch Percentage (%)')
then avg(`Total`)
end)