Exclude first Value - Rank & Window

leeloo_dallas
leeloo_dallas Member
edited May 2021 in Magic ETL

I am currently calculating the contribution for my dataset and have already sorted my values in descending order using the rank tile with date and group for my partitions.

What I'm hoping to do is sum the previous value + the following value until my last value would be the grand total.

In theory, I thought using the rank & window tile would be ideal, but the issue arises in the first value. If you notice in the table, it calculated the first value of the partition twice.

Screen Shot 2021-05-21 at 6.44.07 PM.png


Setting the preceding to unbounded and following to 1, I was able to achieve the results I wanted but with one teeny tiny issue: those first values.

Screen Shot 2021-05-21 at 6.46.24 PM.png


Do you have any suggestions? I'm so close, yet so far.

Tagged:

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    :P when asking a question in Dojo,a lot of times a proper samle dataset and plain english description of what you're doing helps! Good luck.


    Just keep in the back of your mind, if you want the % of total per month you just have to partition by year / month.

    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"

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    It's a little unclear of what you are trying to do, but if you change 1 following to 0 following, that should eliminate the total in the first row.

    If you are really want to previous values and next values then consider using LAG for previous and LEAD for following and then you could total them together in the next tile.

    **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.
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    +1 for Mark's advice.


    Please state in 'plain English' what you're trying to accomplish.


    I don't understand why you're taking the cumulative sum of contribution sorted by video_plays desc. it's probably not accurate to assume that you can take a cumulative sum per day and domain.


    if i had to guess you're trying to calculate a subtotal of some sort per day. you can't use the rank / window function in Magic for that, you'd have to use a GROUP BY + JOIN the data back in.

    if you are trying to calculate a cumulative sum, be careful of your PARTITION BY clause as that will reset the counter each day for each domain.

    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"
  • leeloo_dallas
    leeloo_dallas Member
    edited May 2021

    Hey @MarkSnodgrass apologies for the late response.

    I was going to try your suggestion before I realized it doesn't fit the aggregation I'm trying to pursue. I tried it anyways, but what it did was move the sum downwards.

    For more context, my date column and my video title will be what makes the partition.

    All values in my date column are the same, except for month and the year since the data didn't come with exact dates, so I had to make due with a means to distinguish when the values occur.

    The preceding and following I mentioned is the correct aggregation I think I'm going for. Since I thought unbounded preceding is to sum preceding value + the following value. Unfortunately, my issue arises when I want the first row to remain the same. Which I think, would be the cumulative sum @jaeW_at_Onyx, as Onyx mentioned. Although, I wouldn't want to use the grand total, since there's much emphasis keeping these values with their assigned month.

    Example:

    Table

    Date Name Value Result

    10/01/20 A 1 1

    10/01/20 B 2 3

    10/01/20 C 3 5

    11/01/20 D 1 1

    11/01/20 E 2 3

    11/01/20 F 3 5

    What I will eventually be able to do, is perform an 80-20 analysis. Typically, I would do this in excel, but I wanted to see if it can be accomplished in DOMO. I attached an example doc complete with my formulas still attached, in hopes that it would help.

    example_sheet.xlsx


  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    I laughed. Because, this video is already on YouTube. https://www.youtube.com/watch?v=Esnu1PSxRjM&t=26s

    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"
  • leeloo_dallas
    leeloo_dallas Member

    Hahahahahaha lololol wow my research must be awful.

    Thanks for the link Onyx!

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    :P when asking a question in Dojo,a lot of times a proper samle dataset and plain english description of what you're doing helps! Good luck.


    Just keep in the back of your mind, if you want the % of total per month you just have to partition by year / month.

    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"
  • leeloo_dallas
    leeloo_dallas Member

    @jaeW_at_Onyx Do you still have the JSON code to perform it in ETL?

    I will need to be able to filter it in magic etl.

  • leeloo_dallas
    leeloo_dallas Member

    I can't seem to get it to work for me in Views Explorer.

Categories