Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
When building an app, there are certain functions that I use over and over again. This is the case when I need to add a Quarter field to my data model. I often have dates in my apps and sometimes I like to provide the user with a Quarter field so I thought I would share the script that I often use. Below is the script I always use to create a Quarter field in my data model.
When creating the Quarter field, I use the Ceil function. The Ceil function can be used in the script or a chart and it rounds a number up to the nearest multiple of the specified step interval. You cannot talk about the Ceil function without mentioning the Floor function. The Floor function does the opposite of the Ceil function by rounding numbers down. Both functions have a third parameter, the offset, which allows you to increase or decrease the result of the Ceil or Floor function by a specified value. Let’s take a look at some examples.
The syntax for the Ceil function is:
Ceil(x[, step[, offset]])
- Ceil(2, 4) – returns 4. The step is 4 so you can think of these values 0, 4, 8 and so on. The number 2 is between 0 and 4 and when rounded up will return 4.
- Ceil(4,2) – returns 4. The step is 2 so you can think of these values 0, 2, 4, 6 and so on. The number 4 is the same as one of the step values so it returns 4.
- Ceil(5.5, 3) – returns 6. The step is 3 so you can think of these values 0, 3, 6, 9 and so on. The number 5.5 is between 3 and 6 and when rounded up will return 6.
- Ceil(5.5, 3, 0.5) – returns 6.5. The step is 3 so you can think of these values 0, 3, 6, 9 and so on. The number 5.5 is between 3 and 6 and when rounded up will return 6. Since there is an offset value, we need to increase 6 by 0.5, giving us a final result of 6.5.
- Ceil(4.111) – returns 5. There is no second parameter in this example, so 1 is assumed. You can think of the values 0, 1, 2, 3, 4, 5 and so on. The number 4.111 is between 4 and 5 and when rounded up will return 5.
The Floor function does the same as the Ceil function except instead of rounding up, it rounds down. Let’s take a look at the same examples and see how the results vary.
The syntax for the Floor function is:
Floor(x[, step[, offset]])
- Floor(2, 4) – returns 0. The step is 4 so you can think of these values 0, 4, 8 and so on. The number 2 is between 0 and 4 and when rounded down will return 0.
- Floor(4,2) – returns 4. The step is 2 so you can think of these values 0, 2, 4, 6 and so on. The number 4 is the same as one of the step values so it returns 4.
- Floor(5.5, 3) – returns 3. The step is 3 so you can think of these values 0, 3, 6, 9 and so on. The number 5.5 is between 3 and 6 and when rounded down will return 3.
- Floor(1.1, 1, 0.5) – returns 0.5. The step is 1 so you can think of these values 0, 1, 2, 3 and so on. The number 1.1 is between 1 and 2 and when rounded down will return 1. Since there is an offset value, we need to decrease 1 by 0.5, giving us a final result of 0.5.
- Floor(4.111) – returns 4. There is no second parameter in this example, so 1 is assumed. You can think of the values 0, 1, 2, 3, 4, 5 and so on. The number 4.111 is between 4 and 5 and when rounded down will return 4.
So now let’s apply the Ceil function to determine the quarter from a date. The script I like to use is:
If we break this down, we first use the Month function to return the month number and then we divide that by 3 since there are 3 months in a quarter. So assuming the OrderDate is December 11, 2015, Month(OrderDate)/3 will return 4 (12/3=4). Then the Ceil function is used to round that number up. The script above also uses the Dual function so that we can display the Quarter as a string, Q4, with a numeric value of 4. This makes it easier to sort charts that use the Quarter field.
The Ceil and Floor functions provide rounding capabilities with the ability to set the step as well as an offset. If I had a cheat list of scripts I like to use, this Quarter script would be on the list. What would be on your list?
Thanks,
Jennell