# 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

Tagged:

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.” -Superman
______________________________________________________________________________________________

• 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.
• 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

• 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.