Granularity Issues in Domo

bradyprice19
bradyprice19 Member
edited July 2023 in Beast Mode

Hello, I have a dataset that I'm working with that has my data at a granularity that is quite large.

I then want to be able to group that by each individual session that happens on our website and sum the total time spent for that session. I then want to take the average across all sessions. But I want to be able to do that dynamically dependent on what the user filters.

For example, I want a user to be able to filter to a particular country (say United States) and for it to sum the total time spent on the website for each session under that country and THEN to take the average across all sessions. But that filter will change dependent on what the user will filter on the page and so I can't hard-code it into the ETL but needs to be a beastmode.

I thought of first doing a window function but I couldn't figure out how to get it to work in this scenario since I'm changing the granularity of the data a couple times from the original granularity. Any ideas?

Tagged:

Best Answer

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    You could use either a Window Function (external video) or the Fixed function, but Beast Modes are no good to do aggregation of aggregations. If the aggregation will happen at a single level, then on either the OVER clause of your window function or the FIXED clause of the fixed function, instead of using a specific field, you'll enter the logic on the granularity selected.

    Another option is to do some aggregation first at ETL (most likely with a view) to the highest level of granularity that still contains all the data detail you need for this, and then just roll it up with beast modes or depending on the base card.

    If you could provide a sanitized sample of what the raw data looks like and what the ideal outcome would look like that could help get a better sense on the available options.

Answers

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    You could use either a Window Function (external video) or the Fixed function, but Beast Modes are no good to do aggregation of aggregations. If the aggregation will happen at a single level, then on either the OVER clause of your window function or the FIXED clause of the fixed function, instead of using a specific field, you'll enter the logic on the granularity selected.

    Another option is to do some aggregation first at ETL (most likely with a view) to the highest level of granularity that still contains all the data detail you need for this, and then just roll it up with beast modes or depending on the base card.

    If you could provide a sanitized sample of what the raw data looks like and what the ideal outcome would look like that could help get a better sense on the available options.

  • bradyprice19
    bradyprice19 Member

    Hey Marcel! Thank you! A fixed function solved my issue! I obviously need to do some more research into Fixed functions because this honestly is amazing how it solves my issue:

    image.png

Categories