Best Of
Re: Domo IDEAs Conference - Beast Modes - Rolling Averages
You wouldn't need the /4 and your total shipped should be surrounded in a parenthesis.
(LAG(SUM(`OT Shipped`)) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)) +LAG(SUM(`OT Shipped`),2) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2))+LAG(SUM(`OT Shipped`),3) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)) +LAG(SUM(`OT Shipped`),4) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)) / (LAG(SUM(`Total Shipped`)) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)) +LAG(SUM(`Total Shipped`),2) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2))+LAG(SUM(`Total Shipped`),3) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)) +LAG(SUM(`Total Shipped`),4) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)))
Re: Beast mode difference shows blank even when using coalesce
Dang @MarkSnodgrass I thought I was faster than you!
His solution is the better one since you're wanting to calculate the difference and don't always want 0 displayed for your null values. You will need to come up with a cross product to determine all possible year + client + state combinations, left join your original dataset to this new combination dataset, do some aggregation to sum them all and then use the resulting dataset in your pivot table. As Mark mention you can use the window function as well.
Until next time @MarkSnodgrass ...
Re: Beast mode difference shows blank even when using coalesce
Hi @MattLynn
It's because there's no data for Domo to evaluate in your pivot table to even apply a coalesce on. What you can do is under the General Properties of your pivot table you can check the "Fill Empty Data Cells With 0" option to tell Domo to display 0 instead of blanks in your pivot table.
Re: Group by Month, sorting by Year with that month
Hi @user031170
You can use a beast mode to calculate the Month for your date and another for the year of your date:
MONTH(`dt`)
YEAR(`dt`)
Then sort based on those two beast modes. Are you also graphing by month in your date selector in the upper right?
Re: How populate a new column with data based on data in other rows
Unless you know each of those events are for the same user you won’t be able to tie different sessions together because each visitor to your website will get a new session ID if they haven’t visited your site within 30 minutes or whatever you have your Google analytics configured to. I am not certain if you can use googles user identification to tie all of the different sessions together but that might be something you can also look into. If that is the case you can use the rank and window tile to partition based on googles user identifier and then order by event time and use the lag too pull your user id field
Re: How populate a new column with data based on data in other rows
If you're simply wanting to do a translation stating they're authenticated you can use a Beast Mode on a card or a Formula Tile in a new Magic ETL dataflow to define a new column. I'm not certain what you logic would be to determine if they're "Pre-Authenticated" but assuming the user ID is populated means they're authenticated you can do something like this:
CASE WHEN `UserID` IS NOT NULL THEN 'Authenticated' END
Re: Is possible to use text values in a pivote table?
You could attempt to use a magic Etl and the pivot tile to process your data into the pivot format you’re wanting and then use a standard table to display that information.
Re: How to Create Monthly Averages for Headcount?
I'd recommend using a custom date dimension with offsets so you can easily have this month and last month together based on your current month. I've done a more detailed writeup on it here: A more flexible way to do Period over Period comparisons You may need to tweak it if your data is on a monthly cadence instead of daily.
Then you could use a beast mode like:
(SUM(CASE WHEN `Period Type` = 'Current' THEN `Headcount` ELSE 0 END) + SUM(CASE WHEN `Period Type` = 'Last Month' THEN `Headcount` ELSE 0 END)) / 2
Re: How to Create Monthly Averages for Headcount?
You'll need to talk with your CSM to get Window Functions turned on as it's a feature switch they need to do.