increase - date to date
Hello,
Maybe somebody can assists how to write the formula for the next issue (ex):
date1 = amount1
date2 = amount2
date 3 = amount3
p.s. amounts are not on daily basis
on the chart (line + grouped bar), I want to show
- for the date2 - amount2 (bar) and sales increase (amount2-amount1)(line)
- for the date3 - amount3 (bar) and sales increase (amount3-amount2)(line)
RG,
Taras
Best Answer
-
This will need to be handled at the data set level. So you will need to create these calculations withing a dataflow.
I'm assuming your data is set up to look something like this:
DATE SALES 3/1/2019 $7.19 3/1/2019 $21.57 3/1/2019 $19.38 3/1/2019 $17.38 3/1/2019 $0.06 3/1/2019 $20.37 3/1/2019 $3.04 3/2/2019 $7.94 3/2/2019 $11.14 3/2/2019 $23.50 3/2/2019 $3.31 3/3/2019 $19.65 3/3/2019 $26.99 3/3/2019 $14.25 3/3/2019 $12.24 3/3/2019 $17.30 3/3/2019 $27.20 3/3/2019 $28.39 3/3/2019 $10.57 3/3/2019 $13.10
What you need to get is something like this for your graph:Date Total Sales Prev Days Sales 3/1/2019 $88.98 $100.00 3/2/2019 $45.88 $88.98 3/3/2019 $169.70 $45.88 This would take a couple of transforms in MySQL dataflow.
1. sum the daily sales
SELECT `DATE`, SUM(`SALES`) AS `Total Sales` FROM orig_table GROUP BY `DATE` ORDER BY `DATE`
2. Find the sales for the previous day:
SELECT DATE_ADD(`DATE`, INTERVAL 1 DAY) as `DATE`, `Total Sales` as `Previous Days Sales` FROM `daily_sales`
3. Combine the two tables:
SELECT d.`DATE`, d.`Total Sales`, p.`Previous Days Sales`
FROM `daily_sales` d
LEFT JOIN `previous_day` p
ON d.`DATE`=p.`DATE`
ORDER by d.`DATE`Then you should be able to make your graph
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1
Answers
-
This will need to be handled at the data set level. So you will need to create these calculations withing a dataflow.
I'm assuming your data is set up to look something like this:
DATE SALES 3/1/2019 $7.19 3/1/2019 $21.57 3/1/2019 $19.38 3/1/2019 $17.38 3/1/2019 $0.06 3/1/2019 $20.37 3/1/2019 $3.04 3/2/2019 $7.94 3/2/2019 $11.14 3/2/2019 $23.50 3/2/2019 $3.31 3/3/2019 $19.65 3/3/2019 $26.99 3/3/2019 $14.25 3/3/2019 $12.24 3/3/2019 $17.30 3/3/2019 $27.20 3/3/2019 $28.39 3/3/2019 $10.57 3/3/2019 $13.10
What you need to get is something like this for your graph:Date Total Sales Prev Days Sales 3/1/2019 $88.98 $100.00 3/2/2019 $45.88 $88.98 3/3/2019 $169.70 $45.88 This would take a couple of transforms in MySQL dataflow.
1. sum the daily sales
SELECT `DATE`, SUM(`SALES`) AS `Total Sales` FROM orig_table GROUP BY `DATE` ORDER BY `DATE`
2. Find the sales for the previous day:
SELECT DATE_ADD(`DATE`, INTERVAL 1 DAY) as `DATE`, `Total Sales` as `Previous Days Sales` FROM `daily_sales`
3. Combine the two tables:
SELECT d.`DATE`, d.`Total Sales`, p.`Previous Days Sales`
FROM `daily_sales` d
LEFT JOIN `previous_day` p
ON d.`DATE`=p.`DATE`
ORDER by d.`DATE`Then you should be able to make your graph
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
You could also use a Period Over Period that compares day over day if you dont want to build a dataflow to achieve the same result
see screenshot
Domo Arigato!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'0 -
Hello,
Yes, that is useful. But not applicable if sales not on daily bases. Am i wrong?
I have the next data
date amount 01.01.2019 100 02.01.2019 200 04.01.2019 400 05.01.2019 500 06.01.2019 250 07.01.2019 100 09.01.2019 500 what i need:
date amount diff 01.01.2019 100 02.01.2019 200 100 04.01.2019 400 200 05.01.2019 500 100 06.01.2019 250 -250 07.01.2019 100 -150 09.01.2019 500 400 and graph
0 -
Oh ok , then you will be better using a flow to bring yesterday's sales into today. either as SuperMan suggested or doing a subquery like the screenshot below
Domo Arigato!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 57 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive