% of Total

Options
leeloo_dallas
leeloo_dallas Member
in Magic ETL

Hey there, I'm trying to create a % of total column for my dataset with various domains.

Issue is, there's multiple rows with the same values as others.

 

Tried to perform a group by domain then rank by video plays with a partition by url, but it is not working. 

Thought maybe a window function could work, but I would need it to be distinct since each url is different.

 

I don't think it's possible to do for each domain individually cause my original dataset has 937k rows and about 30+ domains, but if I have to...

 

I also wanted to know, is there a way to perform rank if you have multiple rows with the same value? 

 

Similar Example of my table

DomainVideo PlaysTopicURL
abc.com1Dinosaursabc.com/hgffdjnhgdjn
bec.com214Hipposxyz.com/gsGEG
abc.com156Alligator bec.com/agreah
bec.com2Rhinoabc.com/gaggb
dbhiaf.com269Bearbhiaf.com/agregher
efg.com569Deerefg.com/fgfhgsh
hikj.com45Kiwihikj.com/hfgshtf
jijk.com92Platypusefg.com/fdsGS
efg.com92Narwhaldbhiaf.com/gsgsge
efg.com92Beaverjijk.com/gsdagre
efg.com879Antjijk.com/gdagaerd
jijk.com214Kangaroodbhiaf.com/gahreah
bec.com1Dolphinabc.com/greagraeg
dbhiaf.com2Giraffedbhiaf.com/graehaerh
abc.com214Emuxyz.com/agrage
xyz.com300Mousexyz.com/fresgr
jijk.com214Snakejijk.com/cdsfaGF

 

I think down the road, I will need to separate the domains from each other and locate % of total that way too, but that may be asking for too much.

Comments

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    it is unclear to me why you need rank for percent of total.

     

    assuming you have Domains on the axis you could get each domain's percent of total with

     

    sum(video_plays) / sum(sum(video_plays))

    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

    Oh! Yeah, I want to get the percent of total on each domain separately, but I don't think it possible. 

     

    And then a rank on each domain separately. 

  • leeloo_dallas
    leeloo_dallas Member

    Oh no, I also am getting an error. It won't let me display this in a table. 

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    ? can you be more specific about the error?

     

    Window Functions require feature enablement, contact your CSM and ask them for "Window functions in Beast Modes"

    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

    I've contacted my CSM.

     

    The window functions are enabled, but I will contact my CSM to see if there's a bug. 

  • leeloo_dallas
    leeloo_dallas Member

    I'll return once the window functions are fixed to accept to the solution. 

     

    It could be my data maybe? 

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    ... there's nothing wrong with Window Functions AFAIK.  It's either enabled or it's not.  From there users usually havea problem in their code.  

     

    I've covered tons of window functions on my YouTube channel, https://www.youtube.com/watch?v=eifSYZIcPzg

    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

    Thanks so much with helping me on this and sourcing me with your content. Your vids have definitely helped me adjust to DOMO a lot easier. I'll have to look at this video later today. 

Categories