GrantSmith Coach

Hi @DANIH Can you put your MySQL code here for us to review?

Hi @Khan You'll want to calculate the percentile rank within a Magic ETL before you pull it into your card. Use the Rank & Window tile and Rank your times. Add a Group By tile to get a Count of the total rows in your dataset. Join it back to the Rank & Window tile so that the total row count is now a column next to every…

Substring starts at position 1 so you'd need to start as position 3 with a length of 2. It'd look something like this using a string operation tile:

You can utilize a formula tile and use the LEFT AND RIGHT functions to get the left 2 digits or the right 2 digits but note that if it’s not 4 digits you’ll get unexpected results. Alternatively you can utilize the sting operation tile to pull out pieces of a string as well

Hi @dacorson Magic ETL 2.0 doesn't know if your format is MM/DD/YY, DD/MM/YY, YY/MM/DD... etc by default. You need to explicitly define your format using a format string within the Alter Columns tile for your field. To do this you need to click the gear icon, then select the Date Settings option. In here you'll need to…

@Daneia Have you tried explicitly pulling the missing days to see if that will pull in the data for you via the Domo FB connector? Alternatively you could export the missing data from the FB platform, upload it into Domo and then utilize a Dataset View to UNION / append the data together and utilize that new dataset view…

You can create your own custom app if you want to do a custom visualization but it'd be a bit difficult to maintain if you're wanting to utilize custom apps for every single dashboard you're wanting. You can check out https://developer.domo.com to see how you can extend the Domo platform. This may also be something to…

Hi @bp_paulo_fernandes User filters are applied to the entire dataset, there isn't really an 'ALL' option like in Power BI. You might be able to aggregate your data via a dataflow and join that to your original dataset. This way each row would have the values from the entire dataset when each row is filtered via the user…

Hi @tobypenn How is your data formatted? You'll likely need it to look like: Quarter | Week | Activities Q1 | 1 | 500 Q1 | 2 | 450 ... Q4 | 13 | 1000 You can then use the Week as the x-axis, Activities as your y-axis and Quarter as your series.

@Daneia What is the issue you're running into? Is it returning an error message? No data? Are you able to pull that data from the Facebook platform using their website?

Hi @jmcgurl You'll want to have your case statement inside your aggregate: COUNT(DISTINCT case whenate WEEKDAY(`datefield`) = 1 AND `SESSION PURPOSE` = 'FULL_STORE_SCAN' AND `REGION CODE`= 'NA' AND `REGION CODE`= 'AP' Then `STORE NO` End)

Glad you got it figured you!

Hi @emmanuel_fabre It appears that the NULLs in your data are causing some issues which is odd because the AVG should handle nulls correctly. In any case I was able to utilize this beast mode to get around the nulls or 0 values as outlined in your excel document: AVG(CASE WHEN `Total Conversions` > 0 and `Total Views` > 0…

@Jbrorby - Thanks for following up and your contribution to the Dojo community!

@User_32612 I'm assuming you're using a beast mode for your x-axis - do you have any fields listed in your sorting? That may throw off your grouping. You may want to come up with a similar beast mode to define your sorting such that anything pre week 16 would be a value of 15 so that they'd sort and group based on the same…

Thanks for the information @jaeW_at_Onyx I'll have to play around more with the syncing in custom apps.

Correct you can only write to an existing dataset in AppDB not in Domo proper

Hi @the_tree What specific issues are you running into? What are the error messages you're getting? Are you trying to write to a new dataset? Has the dataset been correctly defined in your manifest file?

Hi @Daneia , I believe this is because, looking at the connector FAQ tab, it's using version 5.0 of the Instagram API which has been retired. They're on v11.0 now. You could try and reach out to your CSM or support and see if they would get that API connector updated.

Hi @kacy How are you ingesting this data? Are you attempting to do an UPSERT? What it boils down to is that the column you're using to identify a row's uniqueness isn't unique. You'd need to reimport your data such that you don't have duplicate IDs if your keys are set to the column that denotes uniqueness. Alternatively…

Hi @jbandley @MarkSnodgrass offer's a good solution. I was going to suggest another option utilizing SUBSTRING, INSTR and LENGTH but his is a simpler option.

Hi @user069636 Whenever I typically need to do some period over period comparisons I actually restructure my data so that I have a report date and a "comparison date" (the actual date the metric was recorded on) and calculate it as necessary. This allows you to have custom defined periods (like 2 weeks ago and a week ago).…

Hi @nbrx Whenever I typically need to do some period over period comparisons I actually restructure my data so that I have a report date and a "comparison date" (the actual date the metric was recorded on) and calculate it as necessary. This allows you to have custom defined periods (like 2 weeks ago and a week ago). You'd…

@Shumilex How is it not working correctly? What values are you getting for hours <1, 1, 2, 3? Which calculation are you utilizing?

Those are actual tiles within the Magic ETL platform. You can use those in your Magic ETL to convert your string to a date.

In that case if Domo is unable to convert the string directly to a date you may need to convert it to a timestamp first before you then convert it to a date.

Hi @user048760 Was this field imported as a timestamp or is it configured as a string? You can utilize the Set Column Type tile in Magic ETL 1.0 or the Alter Columns in Magic ETL 2.0 to force the data type to be a Date.

@emmanuel_fabre Is your average higher or lower than the Excel value? With your 4-line example would your average be 78678.0625? When using AVG it excludes NULL values from its calculation. If your Beast mode value is higher than the excel value it may be because it's excluding those. Should the conversion rate be 0 if…

Hi @emmanuel_fabre How does the data not line up? Are you calculating an average of the averages or an average of the entire dataset in Excel? Simply put do you want: AVG(Total Views / Total Conversions) OR SUM(Total Views) / SUM(Total Conversions)?

You can also delete accounts from that same menu which you may want to do unless you actually do have 12 different accounts to connect with. Also, @MarkSnodgrass - I'll beat you next time! :-D