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.2K Product Ideas
- 1.2K Ideas Exchange
- 1.3K Connect
- 1.1K Connectors
- 273 Workbench
- 2 Cloud Amplifier
- 3 Federated
- 2.7K Transform
- 78 SQL DataFlows
- 525 Datasets
- 2.1K Magic ETL
- 2.9K Visualize
- 2.2K Charting
- 435 Beast Mode
- 22 Variables
- 513 Automate
- 115 Apps
- 390 APIs & Domo Developer
- 8 Workflows
- 26 Predict
- 10 Jupyter Workspaces
- 16 R & Python Tiles
- 332 Distribute
- 77 Domo Everywhere
- 255 Scheduled Reports
- 66 Manage
- 66 Governance & Security
- 1 Product Release Questions
- Community Forums
- 40 Getting Started
- 26 Community Member Introductions
- 68 Community Announcements
- 4.8K Archive