Anything like Difference matrix, Difference between two pivots, difference within the value field?

MattLynn
MattLynn Member
in Magic ETL

I've searched around but haven't had any luck finding what I need, but I am not sure if I've searched the best way either so please refer me if this is a duplicate.

I'll use dummy data to ask my question, but effectively I need the difference between two values across a couple of dimensions. The goal is different from our own data but the formulas needed are the exact same.

testsales.xlsx
image.png
image.png


Attached is the excel for my dummy data. Basically, the data is sellers with the number of sales they had in a state during some year. The pivot tab has the sellers broken out by each state with the difference of the sales from one year vs the previous year.

The goal is to determine how many more sales they are from breaking even with their previous year's performance. We can see that Gandalf and Obi-wan need 4 and 5 more sales respectively within Texas, whereas Tyrion has already broken even (well technically no sales in Texas but you get the idea).

While this is possible in excel and is currently being utilized, I would prefer to use Domo to visualize a similar setup.

I can't get a difference in the beast mode since the data has to be aggregated across multiple rows and making an ETL for this is doable but I wanted to get the community's take before I go down that route, which seems less flexible for drill-downs.

I can set up a pivot in Domo but the value field is preset aggregations and there doesn't appear to be any custom calc for values.

Any ideas?

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    One option is to utilize the lag window functions to calculate the difference between the current year and last year. The caveat to this is you can't have missing years between each salesperson and state (going from 2019 -> 2021 missing 2020 for example)


    A pivot table would end up looking something like this which isn't ideal but close to what you're looking for I think.

    Screen Shot 2021-10-29 at 1.29.32 PM.png


    Beast modes:

    Last Year Sales:

    LAG(SUM(`Sales`)) OVER (PARTITION BY `Seller`, `State` ORDER BY `Seller`, `State`, `Year`)

    Last Year Sales Diff:

    SUM(`Sales`) - COALESCE(LAG(SUM(`Sales`)) OVER (PARTITION BY `Seller`, `State` ORDER BY `Seller`, `State`, `Year`), 0)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    You can utilize the LAG function in a beast mode to do this without building an ETL. You need to make sure you have Window Functions in Beast Modes enabled. Your CSM can enable it if it isn't. To calculate the difference you would do something like this:

    LAG(SUM(`sales`)) OVER (ORDER by `Year`) - SUM(sales)

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    One option is to utilize the lag window functions to calculate the difference between the current year and last year. The caveat to this is you can't have missing years between each salesperson and state (going from 2019 -> 2021 missing 2020 for example)


    A pivot table would end up looking something like this which isn't ideal but close to what you're looking for I think.

    Screen Shot 2021-10-29 at 1.29.32 PM.png


    Beast modes:

    Last Year Sales:

    LAG(SUM(`Sales`)) OVER (PARTITION BY `Seller`, `State` ORDER BY `Seller`, `State`, `Year`)

    Last Year Sales Diff:

    SUM(`Sales`) - COALESCE(LAG(SUM(`Sales`)) OVER (PARTITION BY `Seller`, `State` ORDER BY `Seller`, `State`, `Year`), 0)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MattLynn
    MattLynn Member
    edited October 2021

    Thanks, both of you! I didn't think to use a window function in the beast mode (I've had it activated for a while now, guess I forgot about it).

    @MarkSnodgrass Using your formula, backwards, does give me the numbers I'm looking for so thanks! The function breaks down when I add extra dimensions, for example depending on how it's sorted the sales of one seller will be subtracted from another, but in general, I see where I can go from there.

    @GrantSmith Great breakdown of your solution, getting this implemented now. The coalesce should make it look nice and clean, thanks! You practically did my work for me! Adjusting what you did for my situation mostly gives what I need, but what I didn't consider in my dummy data is that my sales aren't quite 1 for 1. I'm doing a count of the observations, so in my case instead of an integer it would be a specific item they sold per row which could be a toy, car, or whatever. It all gets squashed into an aggregation. While your setup answers my dummy question perfectly, I have one last roadblock in the way:

    I have the client names blocked out by colors, so I'm assuming I have multiple rows of each one (red and green in particular) due to having more filing types to count. Going to take what you gave me and do some research (I'm not super familiar with window functions) but any head start is greatly appreciated!

    image.png

    Edit:

    Nevermind! I got sidetracked and realized that doesn't matter when it goes into a pivot anyway.

    Thanks again, guys!

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @MattLynn and @GrantSmith be careful about that Partition by seller and state. i think you just want to partition by state.


    check the boundaries between when you switch sellers, you may have unexpected results with that LAG.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • MattLynn
    MattLynn Member

    @jaeW_at_Onyx Good point, I'll mess around with that some more, thanks for pointing that out!

Categories