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

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

Answers

  • 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

  • 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

This discussion has been closed.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!