Which of the following functions must be used with the in function in Splunk?

How does Spunk prioritize conditional case functions? Lets say I have a case function with 2 conditions - they work fine, and results are as expected, but then lets say I flip the conditions. What I see happen when I flip the conditions in the case function the results are not correct. Shouldn't Splunk be able to still check which condition it applies to even though I have flipped the conditions? Example below:

Case: TimeSchedule should output the closest 7th min or 37th min - so every half hour past the 7th min or 37th min from the zipTime_epoch.

Works Fine as output TimeSchedule should be 2021-03-06 23:37:59.000000

| makeresults
| eval zipTime="2021-03-06 23:35:59.000"
| eval zipTime_epoch=strptime(zipTime, "%Y-%m-%d %H:%M:%S.%6N")
| eval lastunzip_hour=tonumber(strftime(zipTime_epoch, "%H"))
| eval lastunzip_min=tonumber(strftime(zipTime_epoch, "%M"))
| eval lastunzip_sec=round(zipTime_epoch%60,6)
| eval TimeSchedule=strftime(case(lastunzip_min%30 < 7, zipTime_epoch-

zipTime_epoch%1800+420+lastunzip_sec,lastunzip_min!=37 AND lastunzip_min!=7, zipTime_epoch-zipTime_epoch%1800+2220+lastunzip_sec,1=1,zipTime_epoch),"%Y-%m-%d %H:%M:%S.%6N")

Does not work fine when case in conditions are flipped- output should be 2021-03-06 23:37:59.000000 instead.

Dates are a common element in many data sources. If a field contains recognizable dates, it will have a date or date time data type. When date fields are used in the viz they get a special set of functionality, including an automatic date hierarchy drill down, date-specific filter options, and specialized date formatting options.

Date functions allow you to manipulate dates in your data source.

Date Functions

Date functions sometimes reference date-specific elements, including the

DATEADD('week', 1, [due date])

0 argument, the optional

DATEADD('week', 1, [due date])

1 parameter, and date literals (#). These are covered in more detail at the end of this topic.

There are several other topics that might be of interest but aren't part of date functions:

• Format how a date is displayed in a viz: Custom Date Formats
• Set default date properties: Date Properties for a Data Source
• Work with fiscal dates: Fiscal Dates
• Use the ISO-8601 calendar: ISO-8601 Week-Based Calendar

DATE

Type conversion function that changes string and number expressions into dates, as long as they are in a recognizable format.

Syntax

DATEADD('week', 1, [due date])

2OutputDateDefinitionReturns a date given a number, string, or date expression.Example

DATE([Employee Start Date])

DATE("September 22, 2018") 

DATE("9/22/2018")

DATE(#2018-09-22 14:52#)

Notes

Unlike DATEPARSE, there is no need to provide a pattern as DATE automatically recognizes many standard date formats. If DATE does not recognize the input, however, try using DATEPARSE and specifying the format.

MAKEDATE is another similar function, but MAKEDATE requires the input of numeric values for year, month, and day.

DATEADD

Adds a specified number of date parts (months, days, etc) to the starting date.

Syntax

DATEADD('week', 1, [due date])

3OutputDateDefinitionReturns the specified date with the specified number interval added to the specified date_part of that date. For example, adding three months or 12 days to a starting date.Example

Push out all due dates by one week

DATEADD('week', 1, [due date])

 

Add 280 days to the date February 20, 2021

DATEADD('day', 280, #2/20/21#) = #November 27, 2021#

NotesSupports ISO 8601 dates.

DATEDIFF

Returns the number of date parts (weeks, years, etc) between two dates.

Syntax

DATEADD('week', 1, [due date])

4OutputIntegerDefinitionReturns the difference between date1 and date2 expressed in units of date_part. For example, subtracting the dates someone entered and left a band to see how long they were in the band.Example

Number of days between March 25, 1986 and February 20, 2021

DATEDIFF('day', #3/25/1986#, #2/20/2021#) = 12,751

 

How many months someone was in a band

DATEDIFF('month', [date joined band], [date left band])

NotesSupports ISO 8601 dates.

DATENAME

Returns the name of the specified date part as a discrete string.

Syntax

DATEADD('week', 1, [due date])

5OutputStringDefinitionReturns date_part of date as a string.Example

DATENAME('year', #3/25/1986#) = "1986"

DATENAME('month', #1986-03-25#) = "March"

Notes

Supports ISO 8601 dates.

A very similar calculation is DATEPART, which returns the value of the specified date part as a continuous integer. DATEPART can be faster because it is a numerical operation.

By changing the attributes of the calculation’s result (dimension or measure, continuous or discrete) and the date formatting, the results of DATEPART and DATENAME can be formatted to be identical.

An inverse function is DATEPARSE, which takes a string value and formats it as a date.

DATEPARSE

Returns specifically formatted strings as dates.

Syntax

DATEADD('week', 1, [due date])

6OutputDateDefinitionThe date_format argument will describe how the [string] field is arranged. Because of the variety of ways the string field can be ordered, the date_format must match exactly. For a full explanation, see Convert a Field to a Date Field.Example

DATE("September 22, 2018") 

0Notes

DATE is a similar function that automatically recognizes many standard date formats. DATEPARSE may be a better option if DATE does not recognize the input pattern.

MAKEDATE is another similar function, but MAKEDATE requires the input of numeric values for year, month, and day.

Inverse functions, which take dates apart and return the value of their parts, are DATEPART (integer output) and DATENAME (string output).

Database limitations

DATEPARSE is available through the following connectors: non-legacy Excel and text file connections, Amazon EMR Hadoop Hive, Cloudera Hadoop, Google Sheets, Hortonworks Hadoop Hive, MapR Hadoop Hive, MySQL, Oracle, PostgreSQL, and Tableau extracts. Some formats may not be available for all connections.

DATEPARSE is not supported on Hive variants. Only Denodo, Drill, and Snowflake are supported.

DATEPART

Returns the name of the specified date part as an integer.

Syntax

DATEADD('week', 1, [due date])

7OutputIntegerDefinitionReturns date_part of date as an integer.Example

DATE("September 22, 2018") 

1

DATE("September 22, 2018") 

2Notes

Supports ISO 8601 dates.

A very similar calculation is DATENAME, which returns the name of the specified date part as a discrete string. DATEPART can be faster because it is a numerical operation. By changing the attributes of the field (dimension or measure, continuous or discrete) and the date formatting, the results of DATEPART and DATENAME can be formatted to be identical.

An inverse function is DATEPARSE, which takes a string value and formats it as a date.

DATETRUNC

This function can be thought of as date rounding. It takes a specific date and returns a version of that date at the desired specificity. Because every date must have a value for day, month, quarter, and year, DATETRUNC sets the values as the lowest value for each date part up to the date part specified. Refer to the example for more information.

Syntax

DATEADD('week', 1, [due date])

8OutputDateDefinitionTruncates the date to the accuracy specified by the date_part. This function returns a new date. For example, when you truncate a date that is in the middle of the month at the month level, this function returns the first day of the month.Example

DATE("September 22, 2018") 

3

DATE("September 22, 2018") 

4

(the sunday of the week containing 9/22/2018)

DATE("September 22, 2018") 

5

(the monday of the week containing 9/22/2018)

DATE("September 22, 2018") 

6

(the first day of the month containing 9/22/2018)

DATE("September 22, 2018") 

7

(the first day of the quarter containing 9/22/2018)

DATE("September 22, 2018") 

8

(the first day of the year containing 9/22/2018)

Note: For week and iso-week, the

DATEADD('week', 1, [due date])

9 comes into play. ISO-weeks always start on Monday. For the locale of this example, an unspecified

DATEADD('week', 1, [due date])

9 means the week starts on Sunday.

Notes

Supports ISO 8601 dates.

You would not use DATETRUNC to, for example, stop showing the time for a datetime field in a viz. If you want to truncate the display of a date rather than round its accuracy, adjust the formatting.

For example,

DATEADD('day', 280, #2/20/21#) = #November 27, 2021#

1, if shown in the viz to the second, would display as 5/17/2022 12:00:00 AM.

DAY

Returns the day of the month (1-31) as an integer.

Syntax

DATEADD('day', 280, #2/20/21#) = #November 27, 2021#

2OutputIntegerDefinitionReturns the day of the given date as an integer.Example

DATE("September 22, 2018") 

9NotesSee also WEEK, MONTH, QUARTER, YEAR, and the ISO equivalents

ISDATE

Checks if the string is a valid date format.

Syntax

DATEADD('day', 280, #2/20/21#) = #November 27, 2021#

3OutputBooleanDefinitionReturns true if a given string is a valid date.Example

DATE("9/22/2018")

0

DATE("9/22/2018")

1NotesThe required argument must be a string. ISDATE cannot be used for a field with a date data type—the calculation will return an error.

MAKEDATE

Syntax

DATEADD('day', 280, #2/20/21#) = #November 27, 2021#

4OutputDateDefinitionReturns a date value constructed from the specified year, month, and date.Example

DATE("9/22/2018")

2

Note that incorrectly entered values will be adjusted into a date, such as

DATEADD('day', 280, #2/20/21#) = #November 27, 2021#

5 rather than returning an error that there is no 31st day of April.

Notes

Available for Tableau Data Extracts. Check for availability in other data sources.

MAKEDATE requires numerical inputs for the parts of a date. If your data is a string that should be a date, try the DATE function. DATE automatically recognizes many standard date formats. If DATE does not recognize the input try using DATEPARSE.

MAKEDATETIME

Syntax

DATEADD('day', 280, #2/20/21#) = #November 27, 2021#

6OutputDatetimeDefinitionReturns a datetime that combines a date and a time. The date can be a date, datetime, or a string type. The time must be a datetime.Example

DATE("9/22/2018")

3

DATE("9/22/2018")

4Notes

This function is available only for MySQL-compatible connections (which for Tableau are MySQL and Amazon Aurora).

MAKETIME is a similar function available for Tableau Data Extracts and some other data sources.

MAKETIME

Syntax

DATEADD('day', 280, #2/20/21#) = #November 27, 2021#

7OutputDatetimeDefinitionReturns a date value constructed from the specified hour, minute, and second.Example

DATE("9/22/2018")

5Notes

Because Tableau does not support a time data type, only date time, the output is a a datetime. The date portion of the field will be 1/1/1899.

Similar function to MAKEDATETIME, which is only available for MYSQL-compatible connections.

MAX

Syntax

DATEADD('day', 280, #2/20/21#) = #November 27, 2021#

8 or

DATEADD('day', 280, #2/20/21#) = #November 27, 2021#

9OutputDate (see notes)DefinitionMAX is usually applied to numbers but also works on dates. Returns the maximum (most recent) of a date field or two dates.Example

DATE("9/22/2018")

6

DATE("9/22/2018")

7Notes

DATEADD('day', 280, #2/20/21#) = #November 27, 2021#

8 is treated as an aggregate function and returns a single aggregated result. This will display as

DATEDIFF('day', #3/25/1986#, #2/20/2021#) = 12,751

1 in the viz and will not have a date hierarchy.

DATEADD('day', 280, #2/20/21#) = #November 27, 2021#

9 compares the two values and returns a row-level value. For dates, that value will be a date, and the results will retain the date hierarchy.

Returns Null if any argument is Null.

MIN

Syntax

DATEDIFF('day', #3/25/1986#, #2/20/2021#) = 12,751

3 or

DATEDIFF('day', #3/25/1986#, #2/20/2021#) = 12,751

4OutputDate (see notes)DefinitionMIN is usually applied to numbers but also works on dates. Returns the minimum (earliest) of a date field or two dates.Example

DATE("9/22/2018")

8

DATE("9/22/2018")

9Notes

DATEDIFF('day', #3/25/1986#, #2/20/2021#) = 12,751

3 is treated as an aggregate function and returns a single aggregated result. This will display as

DATEDIFF('day', #3/25/1986#, #2/20/2021#) = 12,751

1 in the viz and will not have a date hierarchy.

DATEDIFF('day', #3/25/1986#, #2/20/2021#) = 12,751

4 compares the two values and returns a row-level value. For dates, that value will be a date, and the results will retain the date hierarchy.

Returns Null if any argument is Null.

MONTH

Syntax

DATEDIFF('day', #3/25/1986#, #2/20/2021#) = 12,751

8OutputIntegerDefinitionReturns the month of the given date as an integer.Example

DATE(#2018-09-22 14:52#)

0NotesSee also DAY, WEEK, QUARTER, YEAR, and the ISO equivalents.

NOW

Syntax

DATEDIFF('day', #3/25/1986#, #2/20/2021#) = 12,751

9OutputDatetimeDefinitionReturns the current local system date and time.Example

DATE(#2018-09-22 14:52#)

1Notes

NOW does not take an argument.

See also TODAY, a similar calculation that returns a date instead of a datetime.

If the data source is a live connection, the system date and time could be in another timezone. For more information on how to address this, see the Knowledge Base.

QUARTER

Syntax

DATEDIFF('month', [date joined band], [date left band])

0OutputIntegerDefinitionReturns the quarter of the given date as an integer.Example

DATE(#2018-09-22 14:52#)

2NotesSee also DAY, WEEK, MONTH, YEAR, and the ISO equivalents.

TODAY

Syntax

DATEDIFF('month', [date joined band], [date left band])

1OutputDateDefinitionReturns the current local system date.Example

DATE(#2018-09-22 14:52#)

3Notes

TODAY does not take an argument.

See also NOW, a similar calculation that returns a datetime instead of a date.

If the data source is a live connection, the system date could be in another timezone. For more information on how to address this, see the Knowledge Base.

WEEK

Syntax

DATEDIFF('month', [date joined band], [date left band])

2OutputIntegerDefinitionReturns the week of the given date as an integer.Example

DATE(#2018-09-22 14:52#)

4NotesSee also DAY, MONTH, QUARTER, YEAR, and the ISO equivalents.

YEAR

Syntax

DATEDIFF('month', [date joined band], [date left band])

3OutputIntegerDefinitionReturns the year of the given date as an integer.Example

DATE(#2018-09-22 14:52#)

5NotesSee also DAY, WEEK, MONTH, QUARTER, and the ISO equivalents.

ISOQUARTER

Syntax

DATEDIFF('month', [date joined band], [date left band])

4OutputIntegerDefinitionReturns the ISO8601 week-based quarter of a given date as an integer.Example

DATE(#2018-09-22 14:52#)

6NotesSee also ISOWEEK, ISOWEEKDAY, ISOYEAR, and the non-ISO equivalents.

ISOWEEK

Syntax

DATEDIFF('month', [date joined band], [date left band])

5OutputIntegerDefinitionReturns the ISO8601 week-based week of a given date as an integer.Example

DATE(#2018-09-22 14:52#)

7NotesSee also ISOWEEKDAY, ISOQUARTER, ISOYEAR, and the non-ISO equivalents.

ISOWEEKDAY

Syntax

DATEDIFF('month', [date joined band], [date left band])

6OutputIntegerDefinitionReturns the ISO8601 week-based weekday of a given date as an integer.Example

DATE(#2018-09-22 14:52#)

8NotesSee also ISOWEEK, ISOQUARTER, ISOYEAR, and the non-ISO equivalents

ISOYEAR

Syntax

DATEDIFF('month', [date joined band], [date left band])

7OutputIntegerDefinitionReturns the ISO8601 week-based year of a given date as an integer.Example

DATE(#2018-09-22 14:52#)

9NotesSee also ISOWEEK, ISOWEEKDAY, ISOQUARTER, and the non-ISO equivalents.

The DATEADD('week', 1, [due date])0 argument

Many date functions in Tableau take the argument

DATEADD('week', 1, [due date])

0, which is a string constant that tells the function what part of a date to consider, such as day, week, quarter, etc.

The valid

DATEADD('week', 1, [due date])

0 values that you can use are:

date_partValues

DATENAME('year', #3/25/1986#) = "1986"

1Four-digit year

DATENAME('year', #3/25/1986#) = "1986"

21-4

DATENAME('year', #3/25/1986#) = "1986"

31-12 or "January", "February", and so on

DATENAME('year', #3/25/1986#) = "1986"

4Day of the year; Jan 1 is 1, Feb 1 is 32, and so on

DATENAME('year', #3/25/1986#) = "1986"

51-31

DATENAME('year', #3/25/1986#) = "1986"

61-7 or "Sunday", "Monday", and so on

DATENAME('year', #3/25/1986#) = "1986"

71-52

DATENAME('year', #3/25/1986#) = "1986"

80-23

DATENAME('year', #3/25/1986#) = "1986"

90-59

DATENAME('month', #1986-03-25#) = "March"

00-60

DATENAME('month', #1986-03-25#) = "March"

1Four-digit ISO 8601 year

DATENAME('month', #1986-03-25#) = "March"

21-4

DATENAME('month', #1986-03-25#) = "March"

31-52, start of week is always Monday

DATENAME('month', #1986-03-25#) = "March"

41-7, start of week is always Monday

The DATEADD('week', 1, [due date])1 parameter

Some functions have the optional parameter

DATEADD('week', 1, [due date])

1. The

DATEADD('week', 1, [due date])

9 parameter can be used to specify what day is considered the first day of the week, such as "Sunday" or "Monday". If it is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.

For the examples below, 22 September is a Sunday and 24 September is a Tuesday. The DATEDIFF function is being used to calculate the weeks between these dates.

DATENAME('month', #1986-03-25#) = "March"

8

• Because start_of_week is 'monday', these dates are in different weeks.

DATENAME('month', #1986-03-25#) = "March"

9

• Because start_of_week is 'sunday', these dates are in the same week.

The date literal (#)

Examples often use the pound symbol (#) with date expressions. This is the date literal, similar to using quotes for text strings, and it tells Tableau that the value inside the symbols is a date.

Without the date literals, dates may be interpreted as various other data types. For example:

FormatData TypeValue'March 25, 1986'String'March 25, 1986'#3/25/1986#Date#3/25/1986#03/25/1986Floating decimal0.000060421986-03-25Integer1,958March 25, 1986 invalid

For more information, see Literal expression syntax

Create a date calculation

Practice creating a date calculation using the Superstore sample data source.

1. In Tableau Desktop, connect to the Sample-Superstore saved data source, which comes with Tableau.
2. Open a worksheet.
3. From the Data pane, under Dimensions, drag Order Date to the Rows shelf.
4. On the Rows shelf, click the plus icon (+) on the YEAR(Order Date) field.

   QUARTER(Order Date) is added to the Rows shelf and the view updates.

   

5. On the Rows shelf, click the plus icon (+) on the QUARTER(Order Date) field to drill down to MONTH(Order Date).
   

6. Select Analysis > Create Calculated Field.
7. In the calculation editor that opens, do the following:
• Name the calculated field, Quarter Date.
• Enter the following formula:

  DATE("September 22, 2018") 

  00
• When finished, click OK.

  The new date calculated field appears under Dimensions in the Data pane. Just like your other fields, you can use it in one or more visualizations.

  Which character is used in search before a command in Splunk?

  The "search pipeline" refers to the structure of a Splunk search, in which consecutive commands are chained together using a pipe character, "|".

  What are the types of search commands used in Splunk?

  There are six broad categorizations for almost all of the search commands:.

  distributable streaming..

  centralized streaming..

  transforming..

  generating..

  orchestrating..

  dataset processing..

  Which of the following functions can be used to filter null values in Splunk?

  use |where isnull(Device) to get all events where Device is null and |where isnotnull(Device) or |search Device=* / index=A Device=* to get all events where Device is not null.

  How to use match function in Splunk?

  We can use the “match” function with the “where” command also..

  This function takes matching “REGEX” and returns true or false or any given string..

  Functions of “match” are very similar to case or if functions but, “match” function deals with regular expressions..