Best Of

@MarkSnodgrass Just wanted to check back in to say thanks for giving me a boost. The backend data changed a bit since I posed this, and it was with ascending dates instead of descending, so I wasn't just able to plug in what you sent, and didn't quite follow, BUT! the running total/grouping by 30 day did give me an idea to do it (I think?) differently. Instead of doing another Lag function I'm just taking the diff of the diff which allows me to compare 30 day groupings. Side by side which now that I'm typing this I think is what you were saying do? Either way this helped, thanks.

@nicolac

You should be able to get the results that you are looking for with @GrantSmith 's suggestion.

I have done something similar to measure page, or card views within Domo. I use COUNT(DISTINCT `UserID`) to show many how many different users view the page, and then COUNT(`UserID`) to measure how many page views those users have had.

I can also show this information over time in a graph to pick up on trends. By combining count distinct and count values, I can see not only which page had the most total views, but also which page is being used by the most users (which won't always be the same)

For example, I can see that I've had 179 users hitting these pages this year, but only 164 have used the main landing page. This means that at least 15 users have bookmarked our subpages and are not viewing the main page at all this year. If this number were to continue growing, it would cause me to rethink placing announcements on the main landing page as not all of our users have been using it.

Either way, this is easily handled by a beast mode. I would actually recommend it so that your numbers will adjust as you add filters or change the timeframe that the card is looking at.

Hi @nicolac

Can you just do a Count(`field`) in a beast mode or do a count in a group by tile (grouping by the class) in an ETL to get the number of times each class used the tool?

Alternatively you could just select Count from the aggregation option on your field in analyzer.

I believe I came up with a way for you to do this using multiple rank and window and formula tiles in Magic ETL. If you copy this into a blank Magic ETL, you should be able to see all the tiles and what they are doing.

{"contentType":"domo/dataflow-actions","data":[{"name":"Test Days","id":"53c4747b-3ef7-4c46-a2c7-7673ee34d0c2","type":"LoadFromVault","gui":{"x":84,"y":156,"color":3238043,"colorSource":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"c1f4230f-1474-437e-a073-a9e442c0f8ba","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Lag to get previous entry","id":"0b181c69-c181-4f20-b1e3-a70e3993469a","type":"WindowAction","gui":{"x":204,"y":156,"color":null,"colorSource":null},"dependsOn":["53c4747b-3ef7-4c46-a2c7-7673ee34d0c2"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"additions":[{"name":"Lag","operation":{"type":"OFFSET","operationType":"LAG","column":"DaysPast","amount":1,"defaultValue":null}}],"orderRules":[{"column":"DaysPast","caseSensitive":false,"ascending":true}],"groupRules":[]},{"name":"Calc Day Diff","id":"bfaccf28-96e3-4213-81b4-c952ff226acc","type":"ExpressionEvaluator","gui":{"x":312,"y":156,"color":null,"colorSource":null},"dependsOn":["0b181c69-c181-4f20-b1e3-a70e3993469a"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"`DaysPast` - `Lag`","fieldName":"DayDiff","settings":null}]},{"name":"Running Total Unlimited","id":"ffe767a9-e7bc-4089-b13f-afc61d0444a4","type":"WindowAction","gui":{"x":432,"y":156,"color":null,"colorSource":null},"dependsOn":["bfaccf28-96e3-4213-81b4-c952ff226acc"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"additions":[{"name":"Runningtotal","operation":{"type":"FRAMED","operationType":"SUM","column":"DayDiff","preceding":null,"following":0}}],"orderRules":[{"column":"DaysPast","caseSensitive":false,"ascending":true}],"groupRules":[]},{"name":"Create Group Number","id":"99283d04-b4f3-4417-9698-cd0f4573429e","type":"ExpressionEvaluator","gui":{"x":552,"y":156,"color":null,"colorSource":null},"dependsOn":["ffe767a9-e7bc-4089-b13f-afc61d0444a4"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"FLOOR(`Runningtotal`/30)","fieldName":"GroupNumber","settings":null}]},{"name":"Create Running Total","id":"4e8f7536-fdf3-4197-9df6-33a6b8052b65","type":"WindowAction","gui":{"x":864,"y":156,"color":null,"colorSource":null},"dependsOn":["cbb1075c-e956-4197-a08d-ea3f3b3e2dca"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"additions":[{"name":"RunningTotal2","operation":{"type":"FRAMED","operationType":"SUM","column":"DayDiff","preceding":null,"following":0}}],"orderRules":[{"column":"DaysPast","caseSensitive":false,"ascending":true}],"groupRules":[{"column":"GroupNumber","caseSensitive":false}]},{"name":"Test Days 30","id":"e19de3e9-40ba-4a98-b86f-bac8f6d0dbd7","type":"PublishToVault","gui":{"x":960,"y":156,"color":null,"colorSource":null},"dependsOn":["4e8f7536-fdf3-4197-9df6-33a6b8052b65"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"b09b8705-314f-4012-b6a0-a41a6b472ba7","type":"DataFlow","name":"Test Days 30","cloudId":"domo"},"versionChainType":"REPLACE","partitionIdColumns":[]},{"name":"Lag to get previous group","id":"2de813ec-928a-44db-b4e6-f34653d6a63e","type":"WindowAction","gui":{"x":660,"y":156},"dependsOn":["99283d04-b4f3-4417-9698-cd0f4573429e"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"additions":[{"name":"PreviousGroup","operation":{"type":"OFFSET","operationType":"LAG","column":"GroupNumber","amount":"1"}}],"orderRules":[{"column":"DaysPast","caseSensitive":false,"ascending":true}],"groupRules":[]},{"name":"Add Formula","id":"cbb1075c-e956-4197-a08d-ea3f3b3e2dca","type":"ExpressionEvaluator","gui":{"x":756,"y":156},"dependsOn":["2de813ec-928a-44db-b4e6-f34653d6a63e"],"removeByDefault":false,"notes":[],"expressions":[{"fieldName":"NewGroup","expression":"CASE when `GroupNumber` <> `PreviousGroup` THEN 'Y' ELSE 'N' END","settings":null},{"fieldName":"DayDiff","expression":"case when `NewGroup` = 'Y' THEN 0 ELSE `DayDiff` END","settings":null}]}]}

Here is a walk through of what I am doing.

I created a webform of test data using your screenshot that has a single column called DaysPast and used the numbers in your screenshot.

The first rank & window tile does a lag to get the previous DaysPast value and have it as a second column so that I can calculate the difference.

The formula tile "calc day diff" is just subtracting dayspast - lag to create a DayDiff column.

The 2nd rank & window tile creates a running total that is not capped, but will be used to create groups.

The Create Group Number formula tile takes that running total number and divides it by 30 and uses the floor function to round down. This creates some groups for us that we can partition by eventually.

The 3rd rank & window tile uses Lag to get the previous group number so that we can identify if we changed groups and reset the daydiff to 0.

The 3rd formula tile creates a Y/N column to identify when a new group started and then overwrites the daydiff if the new group equals Y

Finally, our 4th rank & window tile will create a running total of the daydiff but is partitioned by the group number so that we don't go above 30.

Hope this works for you.