rado98 Contributor

I would start by breaking up the Account name into "Product Code" and "Fee Type", do this in either Excel or WB. After that join in ETL using the Product Code of that table to the Mapping Table. From there it should be quite easy to generate that output table. I think Sumo might work here or a transposed version of the…

I think for hte first part Beastmode will work well. Just cerate a Beastmode "Week" and use that as the "Date' on your table. If using in a graph "Graph by week" will get this to work (although that is such an obvilous solution that i am guessign it that not apply. For your indexed, nor sure what that data looks like, I…

Hi Building up on DanB's logic, you can probalby need the following too Concat( Year(DateField),'-', Case when MONTH(DateField) <= 6 then 'H1' when MONTH(DateField) > 6 then 'H2' end ) Thsi way you woudl get 2018-H1 2018-H2 2019-H1 And so which you will be able to use as an x-axis

Either create a beastmode that groups dates in yearly halves or create a data set with the grouping and joint to your existing data set. Then use the grouping in the charts

As for the time funtion If your data set doesnt already have every bussiness day of the month (future ones included) on at least 1 row I would add a column with total number of bussiness days for the month. This would need to be done by joining a to the data set I would join to the MTD Quota rows. So, crudely expressed…

The othe way of doing it, although it could generate more confusion is to have more than one beastmode/quick filter to select from

Depending on the card type, either show the average of quota or using no aggregation might work. This will not work if you then change the date grain to say yearly nor will a table total work either. If you need it to work under those scenarios, I would split the data set(sales and quotas), remove the duplicated quotas and…

I dont think it will ever work as you intended it to unfortunately You could set up a bestmode that is instead gives you Year-Month so you would be able to select each individual month. Not ideal but it will work

Depending on the card type, either show the average or using no aggregation might work. Thsi will not work if you then change the date grain to say yearly nor will a table total work either. If you need it to work under those scenarios, I would split the data set(sales and quotas), remove the duplicated quotas and reappend…

I would say start by checking that hte updated dataset is indeed there and that it has the data you expect it to have and the column names are as you expect them. Workbench is known, at least to me, to throw curve balls like that.

You probalby have repeated EmployeeNames When joining if the keys are repeated, extra rows are added, unlke vlookups in excel If you have two of the same on two datasets, 4 rows will be generated. The more repeats the more rows, exponentially.

I dont completely understand your problem but there is a way to work in seconds in Magic ETL. Convert your DATETIME columns to decimal, the this converts time so 0 is 1/1/1970 and each unit equals 1 milisecond

Not sure it will work for future peroids but I think your beastmode would work like this AVERAGE( (case when `AccountingMonth`= '19-06' then `OpProgramPrice` else 0 end) ) - AVERAGE( (case when `AccountingMonth`= '19-01' then `OpProgramPrice` else 0 end) ) In order to calculate things accross rows you need an aggragating…

Do you mind explaining how exactly the issue was fixed? Just in case I ever run into the same problem

Hi, I think using Max(Date) might still be a problem. I can think of a couple of way to do it. The easier one to explain would be: Create a column with the Max Date in ETL, then use that to compare using a Beast mode Alternatively, although more complicated and I would not be able to give you the code without try and…

I cna see an issue with the beastmodes: MAX doesnt work for case statements, at least not in the way everyone would want. The MAX value you woudl be getting is the MAX of the row, which is just tha value in the row. What sort of output card do you need? Table or graphs.

I would contact support about it. If Excel can open the file properly and workbench also should. Alternatively you could find a script to do the conversion, then create a batch file that does the convesion and then uploads the file. I cant write scripts though so I cant help you there.

The user could download the dataset themselves directly to excel using the Excel Domo plugin.

I am not sure I understand what your are trying to do. Does the CSV file uploaded every 4 hours contain all records since the beggining of time or is there new data evety 4 hours? If you can elaborate I might be able to give you a simpler solution than below. Use the info in the thread below to generate an appeding CSV…

Workbech has the option of using space as a Field Delimiter

Filter your data set for Site A, Copy or rename Spend A as Final Spend Filter your data set for Site B, Copy or rename Spend B as Final Spend Append

Hi Beastmodes cannot handle a combination of aggregate and non-agregate function. They will either look at individual rows or a set of rows. I find this to be a key handicap of domo vs say Excel. You need to get the ceiling value using ETL and then compare in the card

Two ways of doing it (for me anyways): IF you need this as the calendar for your whole instance, ask your Domo rep to set it up as a Fiscal Calendar If this is for a specific card: Create a spreadsheet with the following columns adjsuted for your new calendar Date, Adjusted Year, Adjusted Week First Day Join them using ETL…

Regarding removing columns, I prefer to reassurance of multiple triggers to ensure no data loss (we mostly use on-site data). I had been putting off setting up a few appending ETLs as the recursive procedure seem too tidious. I am very happy with the simple outcome.

A Fiscal Calendar is always the best, but if you need something on the meanwhile you can do the following: * Create a spreadsheet/dataset with two columns: Date and corresponding School Year. * Join them using ETL. Now you will have School Year Column You will probably need the same spreadsheet for the fiscal calendar…

I had never build the recursive ETL before I suggested it to be honest. I started building an appending/remove duplicate row type recusive ETL, you may want to try this alternate, simpler, method. I am still in the testing phase and unfortunatelly I am to busy too monitor it well enough. The first and second steps are the…

The other (or complementaty) way to do it horizontatly is to allow text to wrap in General Properties. The way I do it vertically: Ctrl+Scroll Wheel.

Worse case if you cant get the function of work in one step: Split in to two using two filter tiles. Keep in mind that empty string cells are considered different to null, allow for both if need be. Rank the stream one without nulls. Append the two streams together.

Search for Recursive ETL in the help section. That will append new data. Other connectors have a option to append new data or replace, the Google Sheet one unfortunately does not. Maybe put in a request to updatethe connector.