Divide numbers from same column based on criteria from another column

chetan_cricut
chetan_cricut Member
edited March 2023 in Beast Mode

I have the following dataset, and would like to create a DAU/MAU ratio for each day. I would want to create a beast mode that powers this number and can be used to generate a line chart at daily level.

image.png


Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    You can use a case statement for this

    SUM(CASE WHEN `category` = 'DAU' THEN `users` END)
/

SUM(CASE WHEN `category` = 'MAU' THEN `users` END)

    Then make sure to graph based on your date field. You can change the aggregation function if you need something different.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • chetan_cricut
    chetan_cricut Member

    Thanks @GrantSmith however, this will only give me a value when date is 30th Jan. How can I divide other dates with 30th Jan?

  • GrantSmith
    GrantSmith Coach

    When should the Jan 30th date be used? For All dates in January or just some of them?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • chetan_cricut
    chetan_cricut Member

    for every date of daily

  • chetan_cricut
    chetan_cricut Member

    Any luck here @GrantSmith ?

  • GrantSmith
    GrantSmith Coach
    edited March 2023

    You can utilize a window function to pull the MAU for each month and divide your DAU by that number

    MAX(CASE WHEN `category` = 'DAU' THEN `users` END)
/
MAX(MAX(CASE WHEN `category` = 'MAU' THEN `users` END) FIXED (BY LAST_DAY(`date`)))


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • McSQL
    McSQL Domo Employee

    GrantSmith's suggestion should work fantastic. Another option is to restructure the data in an ETL. Add a column to the data set that is MAU instead of having it as a dedicated row in the data source. Either way will work, just depends on what technology you are most comfortable with.

    **Say “Thanks" by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories