Best Of
Re: Exclude first Value - Rank & Window
+1 for Mark's advice.
Please state in 'plain English' what you're trying to accomplish.
I don't understand why you're taking the cumulative sum of contribution sorted by video_plays desc. it's probably not accurate to assume that you can take a cumulative sum per day and domain.
if i had to guess you're trying to calculate a subtotal of some sort per day. you can't use the rank / window function in Magic for that, you'd have to use a GROUP BY + JOIN the data back in.
if you are trying to calculate a cumulative sum, be careful of your PARTITION BY clause as that will reset the counter each day for each domain.
Re: PYDOMO SCRIPTS DEPLOYMENT
Domo Magic ETL scripting tiles.
Re: How can I compare YOY data with an offset of a couple of days?
Thanks for the mention @MarkSnodgrass .
If it were me, I'd create a table with two columns. Date and PromotionTitle.
Then JOIN that dataset to your transactions. Then use CASE statements to compare PromotionTitle = BlackFriday2017 and BlackFriday2018.
Re: Difference between preview and data table
Domo broke / changed functionality in product.
lag(count(`ID`)) over( order by month (`Date`))
you have to add the month(`Date`) column as a real column in the dataset.
Also be careful, laging by month(date) will lag 12,11,10,9, 8 but won't lag with respect to the year.
Re: Leading zeros in csv exports removed
Domo will frequently take it's "best guess" on the data type as you import it. unless you're importing it with Workbench I don't believe you can explicitly set the data type.
what you can do in MySQL or Magic 2.0 is use an LPAD() function to Left Pad the data.
Re: Workbench Job Hanging, Dataset stuck on 'Preparing'
@walker_page Domo support would be the best person to direct that question to as they do have access to error logs.
that said, it looks like you're pushing 9.3M rows of data with each push. Is that a full replace or an APPEND? if it's a replace, can you adopt an incremental load strategy?
https://dojo.domo.com/discussion/2790/appending-data-capture-using-domo-workbench
Re: How to add index number in the dataset
in magic etl
add a constant, 1, called temp
add a rank and window tile.
- rownumber
- order by temp
- no partition.
in a dataset view
rownumber() over ()
Re: Drill Down From Summary To Detail Records
You can use different datasets in your drill path. They just need to contain the same field that is being drilled into. Here is a note from this KB article that may help.
Notes: 1) The new DataSet must contain the same category and series column name(s) you filtered off of in the original DataSet. The column data should also be the same; however, the column in the second DataSet may contain additional data items not found in the original DataSet. Value columns you have filtered off of in the original DataSet do not need to be present in the second DataSet. For more information about category, series, and value columns, see Understanding Chart Data. 2) If your DataSet contains more than one sheet and you want to connect to the data in another sheet, you must first re-upload the DataSet, being sure to select that sheet in the Review your Data dialog. This creates a new DataSet that you can connect to like any other DataSet.
For your KPI card, test drilling into the card and see what Domo is filtering on. This will tell you which column needs to be in drill path dataset.
Re: Adding a line on bar-chart
side note... rewrite your metric
CASE WHEN `Metric` = 'C' THEN sum(sum(`Value`)) over(partition by YEAR(`Date`), `Metric` order by `Date`) ELSE 0 END
Avoid putting aggregations inside of a CASE statement. usually leads to heartache.
Given that it looks like you only want your aggregation to work IF the metric type is C. then write it as
sum(sum(CASE WHEN `Metric` = 'C' then `Value` end )) over(partition by YEAR(`Date`) order by `Date`)