Case Statement with calculation
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
0 -
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
0 -
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)0 -
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)0 -
Thanks, Matt. But nothing come out.
0 -
@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)0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 712 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 104 Community Announcements
- 4.8K Archive