rahul93 Contributor

You can use beast mode: Current Terms/ (Datediff(STR_TO_DATE(concat('01/01/',YEAR(Current_Date()))),Current_Date())/DATEDIFF(STR_TO_DATE(concat('01/01/',YEAR(Current_DATE()))), STR_TO_DATE(concat('31/12/',YEAR(Current_Date))))) Note: I assumed that Current Terms is a field in your dataset. Let me know if it works or not,

The same way you were going to update the dataflow every 15 minutes. Instead of telling the excel job to update every 15 minutes you update it everyday at a certain time. Please comment if you find a better solution.

Abs, What if you use the second point to "force" the dataflow to trigger once everyday after you are certain all 3 of your datasets load. So, give about 20-30 minutes (maybe more depending on the time they take) after all 3 of them are scheduled to run and then force the dataflow to load. Hope this helps,

Abs, 1) You can change the time when the datasets load so that you know for certain the dataset that will load the last. 2) So, if you create an excel sheet with one record in it. Load it through the workbench and add a schedule say "every 15 minutes" to it and also check the "Upload even if data hasnt changed" option in…

Hey @Abs, You can do this in 2 ways: 1) Make the dataflow update only based on one dataset that loads last (meaning that loads after the other. 2) Create a dummy dataset and make the dataflow update every 15-20 minutes. So, this dataset will exist in the dataflow only for the purpose of updating the dataflow. For this…

Lol!

Did you try ROUND(field,4)?

Yep, I dont think the custom charts have made it through. Thanks,

Can you provide some screenshots. Just want to see what kind of 'different' numbers you are getting as the output.

@Ctrix6 Hi, shouldnt it be DAYOFWEEK(`date`) <= DAYOFWEEK(CURRENT_DATE()-1)? If youre not able to match the numbers maybe youre missing yesterday's data. Just a thought.

You can use the same structure to abbreviate the Title instead of putting commas. When the number is in millions, the sub-title becomes way too long. So instead of 1,230 it will say 1.2k or 1k depending on your case statement. 

A question is the Avg score a field or or are you averaging the score? If its a field then you can use a beast mode like this : Case when `AVG SCORE` = 1 then 'your_text' when `AVG_SCORE` = 2 then 'your_text_for_this_score' ................... end add this in your series section. Let me know if you are applying the average…

I would say identify the smallest and the largest usersessionid numbers and maybe use 2 - 3 transforms and break out your data. so basically you are doing the same thing 2- 3 times using less data so it takes lesser time. Let me know if you want a code example

How many rows do you have? is it in millions?

Remove interventions and drag AVG score into the series section. That should do it.

Hi, I dont think this is possible in beast mode (it can be done for each user sesssion individually). It can be done easily in a dataflow, please see below : 1) select avg(probability) as "average probability", usersessionid from table group by usersessionid. (transform name : average_prob) 2) select a.*, b."average…

Hi, You would want the value scale in the series section. Let me know if this works out.

Hi, You can create a beast mode and then when a user clicks on one of the filters the beastmode calculates according to the filter selection and accordingly produces the output. Hope this is what you were asking. 

@SamEich Hi, Since it is a beast mode which does its calculation during run-time which is why it takes the other columns in the chart into account. You could use another field to put in your number of distinct orders and then use it in the chart.

@Randyb Well that query is completely different from what i got from your question. Hope you are on the right path! ?

That should do it for all years.I havent worked with YOY so i cannot tell you. But that code should aggregate all data that falls in a quarter regardless of the year.

Hey @Randyb I have edited the question, use that as the label field (x axis for the vertical bar chart) and use the value field as your value scale. This beast mode will allocate your values accordingly. You will also need a beast mode for the filtering the data to show the chart for one year (if your dataset is not…

@Randyb Let me know if this works: case when DATE_FORMAT(`FirstRODate`,'%m/%d') = '01/01' and year(`FirstRODate`) = year(`FirstRODate`) - 1 and QUARTER(`FirstRODate`) = 4 then 'Q1' when DATE_FORMAT(`FirstRODate`,'%m/%d') = '04/01' and year(`FirstRODate`) = year(`FirstRODate`) and QUARTER(`FirstRODate`) = 1 then 'Q2' when…

This might help you: Case when Month(`your_date_field`) <= Month(current_date) - 1 and Year(`your_date_field`) = Year(current_date) then 'yes' else 'no' end Put this as a filter and check yes.

If youre doing the same thing as the variance calculation that you have in your comment. I think you are doing the right thing. Maybe coincidentally it is a whole number and is the same as last years registrations. I cannot say anything about that as I dont have any insight into the data. Thanks,

try using the TRIM function after the `system_name` <> ''. I think the data might have blank system names as ' ' or maybe with more spaces in them. Also, i dont think using distinct would produce the right result as the value is calculated row by row (my understanding), so the distinct will only match one to one which is…

That error was because you were trying to use an aggregate function inside of a case statement.

Try now had left a random or in the middle :X

I made an edit can you please try that.

I could get it to work :(. try this: CASE WHEN TRIM(`System Name`)='' or `System Name` is NULL then 0 ELSE count(distinct `System Name`) END