Best Of

Hi @Emma

You need to check for your denominator to be zero, not your numerator. Try this (will return 0 if `Conservative Max Qty per plt` is 0)

CASE WHEN `Conservative Max Qty per plt` = 0 THEN 0 ELSE `Total Stock`/`Conservative Max Qty per plt` END

@nshively

I'm not certain what all information you need displayed on your card but you could utilize the LAG window function assuming you don't have any missing months:

Average Head Count (Beast Mode)

( SUM(SUM(`EmployeeCount`)) OVER (PARTITION BY `EOMDate`) + LAG(SUM(`EmployeeCount`), 1) OVER (ORDER BY `EOMDate`)) / 2

I'm glad you got it figured out! I was going to suggest programmatically calculating the number of months to divide by depending on the current year and month (untested - back of napkin):

(CASE

 When YEAR(`EOMDate`) = YEAR(CURRENT_DATE) Then SUM(`TermCount`)/ ((SUM(`ActiveCount`))/MONTH(CURRENT_DATE))

 When YEAR(`EOMDate`) = YEAR(CURRENT_DATE) - 1 Then SUM(`TermCount`)/ ((SUM(`ActiveCount`))/12)

 Else 0

 End)

Case statements are helpful in situations like this where you want to conditionally add things together.

SUM(CASE WHEN `IsRead` = 'True' THEN 1 ELSE 0 END) / COUNT(`Username`)

Essentially we're saying give me a 1 if IsRead is True otherwise give me a 0 and then adding all the values together.

This should allow you to calculate your percentage without having to process it through an ETL and stripping out the False values.

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

Thanks @GrantSmith that seems to have worked

I wasn't far off with the Beast Mode statement. Just missed the / COUNT(`UserName`) at the end.

Thanks for all your help.

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: