Calculate the number of days that an event take place

Options
user16282
user16282 Member
in Magic ETL

I have a giant data set with Order number, profit, coupons, campaigns etc.

I have categorized each order's promotion campaign.

Now I want to calculate the profit per day for each campaign so I can make effective comparisons, but I don't really know how to use beast mode for this. I have a column of order data as well. I have a rough idea of either count(distinct(order date)) for every specific campaign category or use datediff? 

 

Could anyonle help me out please?

 

Sample short verson of my data:

Order Number   Order Date      Promotion Campaign     Profit

123456              05/10/2018          Summer Cool            $123

14567                05/12/2018          Summer Cool            $258

Comments

  • NewsomSolutions
    NewsomSolutions Contributor
    Options

    I'm not sure you'd even need Beast Mode.  If you say start with a Table card.  Your columns would be Order Date, Promotion Campaign, and Sum(Profit) and/or Avg(Profit).  By just removing the details of Order Number you'd get the proper aggregations you'd be looking for. Then if those numbers match to what you were looking for I'd probably change it up to a more visually pleasing card like a Grouped Bar chart or something (depending on how many campaigns you run).  

     

    Hope that helps.

  • user16282
    user16282 Member
    Options

    Thanks for your quick response.

     

    I tried your method to have a table of those three columns. The problem is that I have different number of days for each campaign plus on each day there are different number of orders placed.

     

    The result table only shows the average profit per order each day (sum(profits)/# order).

    I am looking for the average profit per day produced by the campaign overall (sum(profits)/# of days).

  • user16282
    user16282 Member
    Options

    I added subtotal rows and now it calculates what I wanted.

     

    But do you know how I could extract this info out to build other cards? 

    I will also play around with it.

     

    Thanks!

  • NewsomSolutions
    NewsomSolutions Contributor
    Options

    Then you could just leave out date and your fields be   Promotion Campaign, and Sum(Profit) and/or Avg(Profit).  You could put in a column for Max(date) and Min(date) so you know what range you're looking at.  

     

    You can't export subtotals out of the card.  If you're left to only seeing what you want in the total or subtotals...you may want to think about a non-table card.  Those may work out better for you.

     

    I hope that helps.

    Matt

    Please like for thanks.

Categories