tadashii Member

I use a background color to create an illusion of separation

If you want to see all other data points, remove the maximum bar option. Sort by clicking on the sort icon. You can see the top and bottom in one glance. If you have both options, you can with 1) create a rank against the data << more work as rank is only available in magic ETL or MySQL 2) create a drill path ( see below )…

Hi - this can be achieved by doing a lil data modeling. Assume below is the sample data and supposed today is 28 Oct 2019. The red block = last 3 days, green block = last 4 days, the orange circle is just to illustrate that unique user for category c in the last 3 days. Use magic ETL to join the sample data to the "Dim…

Why not just use the default of the single bar? Set the max bar and you can sort the top/bottom by just click on the A->Z icon on the top

Hiya - I just ran a small test in magic ETL using an input of 10.5M records and simply output the result to another dataset. The whole process took < 4 mins. You might want to try to isolate your load process from the actual transformation process to see if the load process is indeed the bottleneck. if it is, then it is…

Try this little hack. Create a beastmode calc to convert your date time to numeric year(`Date`)*1000000 + month(`Date`)*10000 + day(`Date`) *100 + hour(`Date`) Then use that new calc as a filter After applying the new calc as a filter, the result is as follows

I am not sure how the performance would be like on a large dataset, but on a sample data, it can be achieved in 2 steps. Step 1 - Create a transformation call rank_table SELECT `Account`,`Date`,`Cost`, @curRank := @curRank + 1 AS rank FROM `blank_row`, (SELECT @curRank := 0) tmp ORDER BY `Account`, `Date` Step2 : Apply the…

Normally the add column is a one-off process and is a separate syntax from the update clause. From your syntax, there are two "from" and no "join" clause <- which is a clear indication of a cross join. from `iuo_geostats_1683748494` geo from `adwords_locationcriterionservice_08142019` loc What I would so is a left join…

Hi - You can set up the dim date in various ways:- 1) append one year of data to the dim date (manual every year) 2) append 100 years of data to the dim date (manual one off) 3) write a SQL procedure to populate the dim date (auto yearly) The magic ETL that does the join will simply take the daily sales data ( that gets…

Hi there - Assume your daily data looks like this Daily Data And you set up your Dim Date as below. Note the WeekNoCustom column at the end Dim Date Then join both datasets using Magic ETL. You may want to change the WeekNoCustom's data type to string to make it easy. Summarize by WeekNoCustom in Analyzer Analyzer Hope…

HI - I would probably use another table which contains all the date info and join to your daily sales data using the date key. The date table will probably look like this. So 25 Aug - 31 Aug 2019 will belong to week 35. Alternatively, in beast mode, try WEEKOFYEAR(`Date`)

I often use the SQL to do my ETL work as I get more flexibility ? Original data in Column 4 has 2decimal places. So rounding it to 2 decimal places has no effect. Column 5 has 3 dp. As you can see below. using a round() and truncate() changes the number.

Hiya, The first screenshot shows the data in raw form entered into a webform. Column 1 is deliberately set to have an insane amount of decimals. Column 4 is set to show the rounding behavior based on the data type. In magic ETL, I converted each column into Whole, Fixed, Decimal. Then in Analyser, I set the column to a…

remove the ; at the end of the syntax case when `url` like '%goo%' then 'google' when `url` like '%adCREASIANs%' THEN 'By Gina' when `url` like '%Aerovex SystemsInc%' THEN 'Hemp Beauty' else 'na' end

Try this to cap to 10. Create the rank and then apply the filter in the dasboard. https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/03ETL_Actions%3A_Rank_and_Window

hmm.... I just realized think it is a lil buggy. Unfortunately, the max number of bars doesn't seem to apply. So if you have more than 10 markets, all the market will appear in the chart if the setup is done as shown in the screenshot. I'll play around a lil bit more to see if can cap it @ 10. 

is this what you are aftering?

I used the method describe in https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Dynamic_Pivot to pivot my data most of the time. It works well. Just need to be careful about the apostrophe and quote. The script below correspond to each of the objects in the attached image "transform" raw data transform…

Is this what you are aftering ?

Heya - Had a play with this although with not much more success as I am not sure how the overall (column O) number should look like. But to get the individual account, I added Account to the filter and changed the beast calc to: 2* (`CostA` - min(`CostA`) over (partition by year(`Date`),`Account`)) / (max(`CostA`) over…

In hindsight, the ABS should be placed after AVG so that you can average the absolute numbers correctly ? ROUND(AVG(ABS(CASE

and to get between -1 and 1 I used : 2* (`CostA` - min(`CostA`) over (partition by year(`Date`))) / (max(`CostA`) over (partition by year(`Date`)) - min(`CostA`) over (partition by year(`Date`))) -1

Ah sorry - I was using a different axis (my bad). Let me try again. Attached is a simple sample data set that I used (just focusing on CostA) CostA" = (`CostA` - min(`CostA`) over (partition by year(`Date`))) / (max(`CostA`) over (partition by year(`Date`)) - min(`CostA`) over (partition by year(`Date`)))

See if this works : ) Create a formula in beast mode : case when `Account Name` = 'Cost' then (`Value` - min(`Value`) ) / (max(`Value`) - min(`Value`) ) end

Using my own dataset, I can get the beast calc to show in the summary value: The difference is I moved the ABS to the front. Hope that helps CONCAT( round(abs(avg(case when (`Account Name`) = 'Units' then `Value` end )),0) , ' Units | ' , round(abs(avg(case when (`Account Name`) = 'Price' then `Value` end )),2) , ' ($) |'…