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:

Best Answer

  • ST_-Superman-_
    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:

    DATESALES
    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:

    DateTotal SalesPrev 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

Answers

  • ST_-Superman-_
    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:

    DATESALES
    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:

    DateTotal SalesPrev 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

    Screen Shot 03-29-19 at 11.18 AM.PNG

    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'
  • Hello,

    Yes, that is useful. But not applicable if sales not on daily bases. Am i wrong?

    date-to-date.PNG

    I have the next data

    dateamount
    01.01.2019100
    02.01.2019200
    04.01.2019400
    05.01.2019500
    06.01.2019250
    07.01.2019100
    09.01.2019500

     

    what i need:

    dateamountdiff
    01.01.2019100 
    02.01.2019200100
    04.01.2019400200
    05.01.2019500100
    06.01.2019250-250
    07.01.2019100-150
    09.01.2019500400

    and graph

    date-to-date-ex.PNG

  • 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

     

    Screen Shot 04-01-19 at 02.01 PM.PNG

    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'
This discussion has been closed.