Aggregation by month with segmentation

Stefan

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

GrantSmith

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

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:

GrantSmith

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

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:

Stefan

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