-
how can I dynamically filter out one min score and one max score in a dataset per quarter
basically, its a survey dataset that have a score of 0 to 10.. every quarter, we need to exclude 1 lowest and 1 highest. we may have multiple lowest or highest, we just need to pick one of each for the quarter. for example, for January, we have the lowest of 5 and highest of 9. so we exlcude one 5 score and one 9 score.…
-
Fastest way to append? Magic seems a bit slow
Hello, I am appending 2 datasets that have a only a few common columns to give me and my clients the ability to bring their plans in without having to join them and cause some duplications. With that being said, does anyone know of a faster way to do this beside append in Magic? I know that fusion cannot do this because…
-
Show columns in rows in a table
Hello, is it possible to show the columns in rows in a table card?? I have this… salesdiscountcostEbt5000100030001000100050800150 I wish that… sales50001000 discount100050 cost3000800 Ebt1000150 Thanks!!
-
Show data from current month plus 11
I have been reading the posts about filtering data by the current day/date. They haven't helped my question. I want to show forecast data for 12 months starting with the current month. All dates in the data set are first of the month. Can I filter in the dataset with ETL? Would it be better to filter in the card? If so,…
-
Time taken for ETL Flow
Hi I have a question regarding the time taken for running an ETL flow. I have this AWS object, which has about 90 Million rows and is getting updated by about 10k to 20 k rows on a daily basis. And we have a scheduled update design to run daily. The thing is- we notice that even though only 10k rows are getting added /…
-
Beast Mode Summary Number Total Count for Data 12 months ago
I am using period over period card. I would like to be able to show in the summary the total "lifts" for the current month last year. I'm going to concat that with the sum of the current months lifts. But I can't quite figure out how to get the beast mode to capture only lifts from 12 months ago. Field name = "date" and…
-
IFNULL - Replace Blank with Text
I am attempting to use the IFNULL formula to say if the Data Table column "Worksite" is blank, replace the blank with the text "No Worksite Assigned." I created a valid formula, however, the resulting chart is still blank and does not have the text. IFNULL(`Worksite`,'No Worksite Assigned') Any thoughts on a formula to get…
-
subtracting a negative number
Could someone tell me how to write a beast mode if subtracting a negative number ex. have a freight charge of 199.68 and want to subract a negative 95.36, the calculation is coming out to be 295.04 instead of 104.32 thanks
-
Data Fusion Capabilities
Has anyone used Data Fusions extensively? I've created a data fusion that combines 4 data sets and in total has about 14 million rows of data at this point across about 120 columns. The reason I choose data fusion was to allow for my team to reprocess data if needed should we find bugs in the data we ingest, to backdate…
-
How do I create a script that assigns a value to a new column in MySQL?
I have opportunities that are listed more than once and I need to create a column that ensures its only counted once. Here's a visual example of what I am trying to accomplish Example data: And what I mean to do is assign a value in a new column the first time its encountered and 0 for every time after. So the opportunity…
-
Error starting a dataflow
We have a dataflow which is run daily to update an issue history dataset. The intent is to have the dataflow start when one of the input datasets is updated. Everything works when the dataflow is started manually, but when I select the option to kick off the dataflow when one of the input datasets changes, an error message…
-
Is there a way in beast mode to split a column by a common character?
Hello, I have a a field where the data comes in abc.123.xyz.789. My goal is to create 4 beast modes, where each beast mode will return one segment of the original field. I looked up how to do in a dataflow using substring_index, but that does not appear to be a valid function in a beast mode…
-
Error "Unable to update to DataSet" in MySQL DataFlow
Hi All , I have problem while executing MySQL Dataflow that always failed, the message error is only "Unable to update to DataSet". Can anyone please help or share, whether you have the same issue ? Thank you ^^ Here is the capture : Based on history, try to re-reun 3 times and always failedError Message on MySQL DataFlow
-
PQTD vs QTD
Hello all, I'm trying to create a beast mode calculation to show the QoQ sales growth as a summary number above the chart. For this how will I be able to create a beast mode to get pQTD sales? Appreciate your help in advance.
-
Join twice on a different column
Hello I have 2 fields sh_state and cn_state which are displaying numbers for states where I'm wanting to display the state abbreviations. The field from the abbreviations table is 'id' so I'm needing to join sh_state with id and cn_state with id so it shows the abbrevition on the same row There are 2 tables, loads and…
-
Employee count by month
I've been asked to build out a card that shows employee count by month, this seems easy enough until you try and factor in the termination date. What is expected is to show in each month the total active employees, so if employee A is hired on Jan 1st and terminated Feb 15th he would show as a count of 1 in January and a…
-
Is there a way to search multiple datasets to see if any are feeding specific data?
I want to see if any of my company's existing datasets include specific data that I would like to start tracking. I know when you open the dataset, you can see what columns of data are feeding, but is there a faster way to check multiple datasets rather than opening each one individually?
-
Beastmode to calculate column based on minimum date case
Hi All, I am running with a error with a below beast mode formula : SUM(CASE WHEN DATE_FORMAT(`Date`,'%m') IN (DATE_FORMAT(DATE(MIN(`Date_INT`)),'%m')) THEN `ColumnA` END) What i am trying to do here is sum column A when the condition for month number is same month number to minimum date(first date) in the column but this…
-
Converting Week numbers into date ranges
I've got some quarterly data that I'd like to format a bit. I'd like to change the "week-1 2015" labels (see attached image) to something like "Jan 1-7 2015" etc. I can't seem to find which beast mode formula I could use to accomplish this. Thanks in advance!
-
How to Make a Period Over Period Beastmode Work with Date Filtering
I am trying to create various period over period beastmodes, some for summary numbers and some for the charts themselves. I have variations using currentDate() in the formula - that works on the default view card, but if you add a drill-down or filter by a historical date range they break. Here is a FYTD/FYTD change ($)…
-
Beast mode using AND and OR
Trying to run sales data to determine how many reps have open opportunities within one of the 5 products as well as of those opportunities which ones have status of closed. I am using this beast mode to find whenever the opportunity status doesn't = a status thats closed and the item is PBCS then put a 1 so I can sum/count…
-
Percentage of Total
Hi How do you create a column in ETL that sums an entire column? I am trying to create a card that gives percentage of total, from looking at other posts the way to do it would be to create a column in ETL which gives the Grand Total of an entire column, example attached, then create a beast mode in a card. But I can't…
-
SUM for sales when there are multiple prices
We have a few places that we receive pricing for a product, so to account for it in our sales beastmode I have been using the below formula but it sometimes provides numbers much greater than the equation should provide. An example would be a quantity of one * a price of 32 has (incredibly infrequently) given a price…
-
How To change the order of transformation in dataflow?
I wonder if it is possbile to change the order of transformation on dataflow? If not how can I ask for this feature?
-
Average Basket Size
I am looking for a way to create an average basket size by date Assuming I have data set that looks like this: InvoiceDateSKUSold Price111Jan 4Product A10111Jan 4Product B20112Jan 4Product C5113Jan 4Product A10113Jan 4Product B20113Jan 4Product C5114Jan 20Product A10115Jan 20Product A10115Jan 20Product C5116Jan 20Product…
-
MAX or SUM With Group BY ID
I'm looking for a Beast Mode formula to create a MAX calculation by ID in my table. My resulting column should look like column B below. I already tried a formula with Distinct and MAX but while the formula was valid, my attempt still failed at producing desired results. Keep in mind that in my scenario, column A is a…
-
Use "Yesterday before 2pm" in a case statement
Hello all! I'd like to use "yesterday at 2pm" in a case statement. How do I write out that condition? The case statement would look a bit like this. Case when 'date' < "Yesterday at 2pm" then 'yes' else 'no' end Appreciate any help.
-
I'm trying to calculate number of days without weekends, can someone please help?
Hi! I'm currently trying to calculate the number of days between the creation of an application and the current day, but I need to subtract weekends, does anyone know how? I do know I can do it under beast mode DateDiff, but I don't know how to remove the weekends. Thanks you!
-
How do I group (summarize) only certain data?
Hi, I have Salesforce data that comes from a couple of different objects. Primary is Opportunities but it's also pulling some information from Accounts. The data would look like this: Account Name Opportunity Opportunity Value Account Value John Doe Opp 1 50 125 John Doe Opp 2 65 125 John Doe Opp 3 30 125 Jane Smith Opp 1…
-
force date sort order
How do I get these dates to sort chronologically? I have the date field in sorting with no aggregation but it's not working.