Exclude first Value - Rank & Window

leeloo_dallas · 2021-05-21T23:52:38+00:00

There was an error rendering this rich post.

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.

Find more posts tagged with

Beast Mode

Data Flows

Accepted answers

jaeW_at_Onyx

: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.

All comments

MarkSnodgrass

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.

jaeW_at_Onyx

+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.

leeloo_dallas

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

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

leeloo_dallas

Hahahahahaha lololol wow my research must be awful.

Thanks for the link Onyx!

jaeW_at_Onyx

: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.

leeloo_dallas

@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

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