RGranada Contributor

Hi @tiffanywijaya, That is a part of the formula where I calculate the number of weeks last year had, in order to subtract that number of weeks (Some years can have 53 weeks) : WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Date`)-1),'%m-%d-%Y'),22) I get the week number for the last day of the year and use that for the…

Hi, Those are two rather similar ways of approaching your problem: First approach: Select * from your_table_name WHERE [Client Name] NOT IN (SELECT [Client Name] From your_table_name WHERE [User Org]=[Client Org]) Second approach: SELECT a.* FROMyour_table_nameAS a INNER JOIN (SELECT [Client Name] FROM your_table_name…

Hi, You can ask DOMO support to enable DOMO metrics datasets in your platform if not enable already. Then from those datasets its easy to build a table card containing, card names, pages, owners, number of views and other interesting statistical information. From that you can export the information. Hope this helps. 

Hi, If I correctly understand, you want to dynamicly create X numbers of columns in a beast mode? Is that right? If so that is not possible, because a beast mode represents a new calculated column by it self. Hope this helps. Don't hesitate to ask if you have any questions. 

I had the chance to try out various approaches to large quantity data modeling/uploading on DOMO when we started our project in 2015. Now usually I select between two data loading schemas depending on the data destination, which type of user will use that data, and the level of reusability I aim for that particular data.…

Hi, You can't use data from 2 different datasets in one DOMO Card, you have to create one dataset with the data already related (Dataflow either ETL, MySQL or RedShift) With the kind of sizes, you are reporting I would always update either by append or upsert in order to get the minimum data refresh interval. In your case,…

I do not have that problem.... Maybe my data sample is not accurate... If you can send me the exact data you are using before the joins, I will try and give it a look. Regards

Hi, I reviewed some of the code and came to the conclusion that I was only joining dates when you have multiple sales per date (ProductGroup, Platform). Made changes to "Proc2" transform : Select n.*,n1.total AS Ly_Total,DATE_SUB(n.Datum, interval WEEK(STR_TO_DATE(CONCAT('12-31-',YEAR(n.Datum)-1),'%m-%d-%Y'),0) week) AS…

Hi, You will have to complete the missing date intervals in your dataset. You can do this by joining the original data set with the following one that provides all the dates in an interval. This the query will take into account all days in the current year disregarding having or not sales. This is the Query: Select a.Dates…

Hi, You are correct in the grouping, my bad! As for the missing Dates, you will have to complete the missing intervals in your original data set. To accomplish that you can use the following Query that returns all the dates in a specific interval and join it to your table in a transform prior to the other query. This will…

Hi, In fact, it should be a continuous logic, it all depends on how you define your year in terms of Week numbers. Try the to change the MySql query to this : Select n.*,n1.total AS Ly_Total,DATE_SUB(n.Datum, interval WEEK(STR_TO_DATE(CONCAT('12-31-',YEAR(n.Datum)-1),'%m-%d-%Y'),0) week) AS Ly_Date From tst_dojo_dod n LEFT…

Yes it will, in order to hide the original field, you will need to use a dataflow, and select just the fields you need to expose to the end user and the same formula to convert the field, something like : Select field1, field2, fieldx, concat(field10, '') from your_table_name Hope this helps. 

Hi, Try this in a beast mode: Concat(Your_Field_Name,'') Hope this helps.

Hi, Upon deeper analysis, I think using a Dataflow will be simple and perhaps the only way to achieve your goal. Here is the MySQL Code you can use in A MySql Dataflow: Select n.*,n1.total AS Ly_Total,DATE_SUB(n.Datum, interval WEEKOFYEAR(STR_TO_DATE(CONCAT('12-31-',YEAR(n.Datum)-1),'%m-%d-%Y')) week) AS Ly_Date…

Is that what your dataset looks like? This is helpful but I would need a sample of the real DOMO dataset. Can you provide it?

OK, good idea... Send me a sample.

Ok, there was another error.... try: SUM(`Total`) - SUM(CASE WHEN `Datum`= DATE_SUB(`Datum`, interval WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Datum`)-1),'%m-%d-%Y'),22) week) THEN `Total` END) / SUM(CASE WHEN `Datum`= DATE_SUB(`Datum`, interval WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Datum`)-1),'%m-%d-%Y'),22)…

Hi, My error, it's just your date field... Here is it corrected: SUM(`Total`) - SUM(CASE WHEN `Datum`= DATE_SUB(`Datum`, interval WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Datum`)-1),'%m-%d-%Y'),22) week) THEN `Total` END) / SUM(CASE WHEN `Datum` = DATE_SUB(`Datum`, interval…

Hi, Let see if we can tackle thin one thing at a time. This beast mode should give you the variance between a weekday and the same weekday last year: SUM(`Sales`) - SUM(CASE WHEN `Date`= DATE_SUB(`Date/Time Opened`, interval WEEKOFYEAR(DATE_FORMAT(CONCAT('12-31-',YEAR(`Date`)-1),'%m-%d-%Y'),22) week) THEN `Sales` END) /…

Hi @ST_-Superman-_, you are right on the count aggregation... As to the MAX(i4w_date) I also found it odd as it was returning the oldest date not the newest one... that was why I swapped it. But I could have missed something. Best Regards,

Hi, If I correctly understood your problem, here's the approach I would take. It involves creating a MySql Dataflow like this (you can use this directly on the output if you do not have other transformations implied): SELECT i4w_userid,Max(i4w_date) as First_Login_Date,Min(i4w_date) as Last_Login_Date,…

Hi, Can you send a sample of your data in excel? Regards,

Hi, I think this post will help you: https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Beastmode-Counting-Unique-fields-that-hit-a-condition/m-p/26727#M3473 Regards,

You're welcome. Best regards.

I am sorry I must be missing something... Isn't that the same as having the SUM of Costumer Value by month for Ws, Makers and Gift? Regards,

Ok, Do you have the "salesfigure" fields calculation set to SUM? Like this: All the 3 fields need to be set this way. Let me know if it worked for you.

No problem... I also overlook that. Best Regards.

Is this anything like what you need: Regards,