-
Could trunc() be modified to accept a time zone parameter?
I find that when I am truncating a datetime to strictly be a date, (dropping the time component) that it is more useful for me to have this function performed in my local time zone rather than in UTC. Would it be possible to add a new parameter to the function that could accept the target time zone?
-
switch function not working with 'dayOfWeek'?
Hi all, I tried to apply a condition to find the latest previous weekday date, so for example, if today is Sunday or Monday, then it will Friday, for other days it will just be yesterday. I tried using switch function like this: switch( datePart(now(), 'dayofweek'), 7, trunc(inc(asDate(now(), 'yyyy-MM-dd'), -2,…
-
Looking to replicate a SUM(COUNTIF) formula in Dataiku
I am working on a scorecard in Dataiku and I would like to calculate the percentage of completion in a set number of columns. Basically, I would like to replicate this formula in excel: =SUM(COUNTIF(ColumnX:ColumnXX,"*")/Total Number of Columns) and am having issues. The columns are a mix of strings, integers, and text,…
-
How to correctly do time conversions
I have a column that has been parsed and is in UTC, when I try to format the date to be in eastern / New York time I get a new column that is -5 hours, but isn't the current the current difference -4 hours? I'm sure this has something to do with daylight savings time vs normal time, but I just want to ensure that my…
-
How to return current date and not time
data set full of jobs and each job has a start date, so I want to create a formula that says filter out my data where my start date hasn't happened yet / less than the current date. The issue I'm having is when I create my now() function it gives me the current date and time, I only need the current date in a parsed date…
-
How to ad leading 0s
I have a column called minutes and that can range from 0 - 60, what I'm trying to do is add a leading 0 to the single digit values, ex 9 = 09, so that no matter what my value is always 2 digits long, is that possible? and if so, how would I accomplish that? Operating system used: windows
-
Week number from day number
We consider that we have a table that contains a column "Day". In this table we have values like "21", "10", "16", .... anyvalue that could correspond to a regular day number from a date. Starting from this, is there ay formula that could return me the week number of the year corresponding to this day number (for this…
-
How to filter dates less than current day
I have a dataset that has a list of dates and days of the week, is there a way I can filter my dataset for dates that are less than or equal to the current date? I tried the formula date = now() but I don't think that's working, it just filters all my data out. Operating system used: windows
-
How to create a sequence number
I have a table with four 3 columns, date, color and time. Im trying to create some kind of sequence field that takes into consideration the first time the color was seen on a specific day. Ex. green was seen on 4/24 at 15:00 and again on 4/24 at 15:30 and again at 15:45, because 3 is the earliest I want that sequence…
-
how to use multiple if functions to create columns
I have 2 columns "label" and "value" and I'm trying to create a third column called category by using the contains function on the label column. I can use if( contains(label,"food"),"food","not food") which basically says if the label contains food, then food else not food. but how can I use multiple contains within an if…