Exclude first Value - Rank & Window
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.
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.
Do you have any suggestions? I'm so close, yet so far.
Best 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"0
Answers
-
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 -
+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"1 -
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.
0 -
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"2 -
Hahahahahaha lololol wow my research must be awful.
Thanks for the link Onyx!
0 -
: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"0 -
@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.
0 -
I can't seem to get it to work for me in Views Explorer.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive