Running Customer Value

user03788
user03788 Member
in Magic ETL

I am trying to build a card that show our customers' running total value (for the last 365 days).

 

Here's the BM I have right now:

SUM(CASE WHEN `SO Ship Date`<=`SO Ship Date` AND `SO Ship Date`>SUBDATE(`SO Ship Date`,INTERVAL 365 DAY) THEN `SO Item Total Amount` ELSE 0 END)

 

Obviously though, when I graph it by month, I'm only getting the sum from the orders in each month. I would like to see the running total, just as of that month.

 

The ultimate goal of this is to see our different sales channels success, or more so, the health of their databases.

 

Any ideas?

Best Answer

Answers

  • RGranada
    RGranada Contributor

    Hi,

     

    Have you considered using one of the running total graphs?

     

    Running Total Bar Graph:

    http://knowledge.domo.com?cid=runningtotalbar

     

    Running Total Line Graph:

    http://knowledge.domo.com?cid=runningtotalline

     

    Running Total Stacked Bar Graph:

    http://knowledge.domo.com?cid=runningtotalstacked

     

    Running Total Grouped Bar Graph:

    http://knowledge.domo.com?cid=runningtotalgrouped

     

    Hope this helps,

     

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • user03788
    user03788 Member

    No, that doesn't quite work.

    I can see how each channel is doing currently. But I don't see any trends.

    The goal would be to see the running total each month, at the same time.

     

    For instance...

    (we sell bar & wine accessories)

    During Q4 I would expect our Liquor Store channel to increase in value.

    During the spring, I would expect to see Wineries have a higher value since that's a big season for them.

     

  • RGranada
    RGranada Contributor

    I'm sorry can't quite understand your need.

     

    Can you draft the chart you are aiming for?

     

    Regards,

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • user03788
    user03788 Member

    Yah, I know it's tricky.

    See attached. That's kinda what I'd expect (it's using the aforementioned formula), accept the values should be much higher.

  • RGranada
    RGranada Contributor

    I am sorry I must be missing something...

     

    Isn't that the same as having the SUM of Costumer Value by month for Ws, Makers and Gift?

     

    Regards,

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • user03788
    user03788 Member
    Answer ✓

    I ended up using an ETL to restructure the data as needed

  • user04014
    user04014 Member

    Hi, would you mind sharing the ETL . I am facing same challenge to create Running total by month.

Categories