Aggregation by month with segmentation

Stefan
Stefan Member
in Charting

Hi there.

I am breaking my head about something that just feels so easy to get. Is there somebody who can help me out with this please?

I want to show a bar chart grouped by month (please see example below). The bars then should be segmented into categories. The data is coming from the user activity in a tool. The idea is to show for each month how many users do have activity on e.g. 1 day that month, 2 days, that month, etc.

I attached the table with example data coming from the data flow I want to use. Each row represents an action done by a user in the system. These actions do have a respective userid and date.

Thanks so much for supporting. It just sounds so easy but I was not able to "beast mode/case when" it...


user-segementation-example-file.xlsx


image.png


Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @Stefan

    Since you're wanting to aggregate an aggregate you won't be able to do it directly in a card. I'd recommend using a Magic ETL dataflow to do your grouping based on the `User ID` field and count how many records you specifically have for each user. Then you can use the new activity count field as your series and use the count of user IDs as your y-axis and date as the x-axis

    Screen Shot 2021-11-12 at 7.52.13 AM.png


    Here's the ETL:

    {"contentType":"domo/dataflow-actions","data":[{"name":"user-segementation-example-file.xlsx","id":"e49f16af-51ba-47c8-b1ab-728cad8e0de6","type":"LoadFromVault","gui":{"x":36,"y":180,"color":3238043,"colorSource":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"cc851b6c-536e-4d75-97d4-3761d3a5ee58","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null},{"name":"Add Formula","id":"19a79f63-28f3-4ccc-8052-d013fa72a151","type":"ExpressionEvaluator","gui":{"x":156,"y":180,"color":null,"colorSource":null},"dependsOn":["e49f16af-51ba-47c8-b1ab-728cad8e0de6"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"LAST_DAY(`Date`)","fieldName":"Month","settings":null}]},{"name":"Group By","id":"500baa72-f4ac-402a-a50f-f1485438884e","type":"GroupBy","gui":{"x":282,"y":180,"color":null,"colorSource":null},"dependsOn":["19a79f63-28f3-4ccc-8052-d013fa72a151"],"removeByDefault":false,"notes":[],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"Month"},{"name":"User ID"}],"partitionedAggregation":false,"fields":[{"name":"Activity Count","source":"User ID","type":"COUNT_ALL","valuefield":null}]},{"name":"User Segmentation Example","id":"ac564bae-7aaa-43d8-86c5-db0bb39082c5","type":"PublishToVault","gui":{"x":408,"y":180,"color":null,"colorSource":null},"dependsOn":["500baa72-f4ac-402a-a50f-f1485438884e"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"8973681c-2028-41ad-8e35-a200177fcd4c","type":"DataFlow","name":"User Segmentation Example","description":"https://dojo.domo.com/discussion/53828/aggregation-by-month-with-segmentation#latest","cloudId":"domo"},"versionChainType":"REPLACE","partitionIdColumns":[]}]}

    You can paste that in the a New Magic ETL dataflow and should populate everything for you. If not here are some screen shots:

    Screen Shot 2021-11-12 at 7.48.37 AM.png
    Screen Shot 2021-11-12 at 7.49.00 AM.png
    Screen Shot 2021-11-12 at 7.49.10 AM.png


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

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @Stefan

    Since you're wanting to aggregate an aggregate you won't be able to do it directly in a card. I'd recommend using a Magic ETL dataflow to do your grouping based on the `User ID` field and count how many records you specifically have for each user. Then you can use the new activity count field as your series and use the count of user IDs as your y-axis and date as the x-axis

    Screen Shot 2021-11-12 at 7.52.13 AM.png


    Here's the ETL:

    {"contentType":"domo/dataflow-actions","data":[{"name":"user-segementation-example-file.xlsx","id":"e49f16af-51ba-47c8-b1ab-728cad8e0de6","type":"LoadFromVault","gui":{"x":36,"y":180,"color":3238043,"colorSource":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"cc851b6c-536e-4d75-97d4-3761d3a5ee58","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null},{"name":"Add Formula","id":"19a79f63-28f3-4ccc-8052-d013fa72a151","type":"ExpressionEvaluator","gui":{"x":156,"y":180,"color":null,"colorSource":null},"dependsOn":["e49f16af-51ba-47c8-b1ab-728cad8e0de6"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"LAST_DAY(`Date`)","fieldName":"Month","settings":null}]},{"name":"Group By","id":"500baa72-f4ac-402a-a50f-f1485438884e","type":"GroupBy","gui":{"x":282,"y":180,"color":null,"colorSource":null},"dependsOn":["19a79f63-28f3-4ccc-8052-d013fa72a151"],"removeByDefault":false,"notes":[],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"Month"},{"name":"User ID"}],"partitionedAggregation":false,"fields":[{"name":"Activity Count","source":"User ID","type":"COUNT_ALL","valuefield":null}]},{"name":"User Segmentation Example","id":"ac564bae-7aaa-43d8-86c5-db0bb39082c5","type":"PublishToVault","gui":{"x":408,"y":180,"color":null,"colorSource":null},"dependsOn":["500baa72-f4ac-402a-a50f-f1485438884e"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"8973681c-2028-41ad-8e35-a200177fcd4c","type":"DataFlow","name":"User Segmentation Example","description":"https://dojo.domo.com/discussion/53828/aggregation-by-month-with-segmentation#latest","cloudId":"domo"},"versionChainType":"REPLACE","partitionIdColumns":[]}]}

    You can paste that in the a New Magic ETL dataflow and should populate everything for you. If not here are some screen shots:

    Screen Shot 2021-11-12 at 7.48.37 AM.png
    Screen Shot 2021-11-12 at 7.49.00 AM.png
    Screen Shot 2021-11-12 at 7.49.10 AM.png


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

    Hi @GrantSmith

    Thanks so much, I was finally able to do this! Great explanation combined with the screens! I just needed to do some tweaks to count unique days per user instead of activities.

    Best, Stefan

Categories