increase - date to date

albert

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

 

ST_Superman

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

ST_Superman

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

Godiepi

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

albert

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

Godiepi

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