Best Of

remember zero and null are not the same. assuming your beast mode is sum(values) / count(values)

if you had 5 rows

0,0, null, 2 and 6, the average is 8/4 = 2

if you had 5 rows

null, null, null, 2 and 6 the average is 8 / 2 = 4.

if you are doing division within the row avg( colA / colB ) as opposed to sum(colA) / sum(colB) you'll get a different result too.

i

You can do this in Magic ETL pretty easily with a formula tile, group by tile and a rank and window tile.

In the formula tile, use the LAST_DAY() function to normalize the dates in each month to the same day, like this: LAST_DAY(datefield)

In the group by tile, group by your last day field and do a distinct count of customer. This will give you a total for each month.

In the rank & window tile, give your field a name like 6-month total choose sum and choose your customer count field that you created in the group by tile. Enter 5 for preceding and 0 for following.

This will give you a 6 month total for each month, assuming you aren't missing any months.

Hope this helps.

I would suggest doing this in Magic ETL since you need to access previous rows. You can do this by filtering to Inherited Sale = True and then join it back to your original dataset and choose left join with your original dataset being on the left side. Join on Engagement ID and and Full Name. Records that show from the filtered tile, will be your Referred By column. The ETL would look something like this:

I would consider creating a Magic ETL that appends all your datasets together into a single dataset that would be used for all your cards on this page. You can add a constant for each dataset before appending to make it easy for you to create the card for each of your "feeder" cards because you can use that constant as a filter behind the scenes on the card to have it display that specific dataset, while still being from a single dataset that all the cards are using. Your ETL would look something like this:

This will make designing and managing your page much easier and allow your "master" card to function as you are hoping.

Hope this helps.

Magic ETL does need a column to be selected. The easiest way to do this is to use the Add Constant tile and create a column with a constant value or do the same with the add formula tile. Then, just add this column to your group by and sum on the field you need to sum up. You can remove afterwards with the select columns tile.

If you are using Domo Workbench you can set the column type to string on the schema tab. If it is coming from somewhere else, you can add the zero back in Magic ETL by using a formula tile and the LPAD function like this:

LPAD(zipfield,5,'0')