Case Statement with calculation

WizardOz
WizardOz Contributor

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

 

Comments

  • 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
    WizardOz Contributor

    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
    WizardOz Contributor

    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)

  • 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
    WizardOz Contributor

    Thanks, Matt.  But nothing come out.  

  • @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)

This discussion has been closed.