Hi,
I am looking to create an expression so my aggregation only includes the current month data. When I searched for this request, I found what can be seen below. Unfortunately, I do not see DATE_TRUNC as an available option in the Expression editor. Is there a different feature/process that I could use to produce the same filtered data?
Thanks.
what about ‘DATETIMETOTEXT’? I’ve stored the current date/time in a variable, then used an expression to truncate to specific date or time, depending on the app requirements.
Hi @codygarcia
For the beginning of the month do this:
rounddatetime(@App info.Current Date and Time , 'month')
For the end of month do this:
subtract_time(add_time(rounddatetime(@App info.Current Date and Time , 'month'), 1, 'month'), 1, 'millisecond')
(same as beginning and then add a month and subtract a millisecond)
now filter
@thorsten.langner, @jasonh Thank you both for the quick response. Below is what I came up with. Will this get me the desired filtering that I’m looking for?
If you want to use it in a trigger condition like that, I suggest to compare it to a rounded Production date (If the date is not the first of month, it will not match).
So
Expression→ROUNDDATETIME(@App Info.Current Date an Time, 'month') = ROUNDDATETIME(@Table >Record.RL Production.Production Date, 'month')→
is true
This will check, if this table records Production Date is in this month
Hi,
I added your suggested Expression to the trigger for each Then, Else If value range (these ranges determine status color).
I know the average production score for March so far is over 2.9, based on the table values, so the Production square should turn red. But as you see below, it remained gray/neutral.
Thanks.
Thins condition does not apply to your whole aggregation. It does not work as a filter an all table data.
What you did is, you checked one specific table record, if it is within this month.
The Table aggregation always refers to a specific query.
I think what you want is, to apply a filter to this specific query.
So you need to adjust the query:
And then map two variables in app to this query (selectedMonthStart & selectedMonthEnd)
whenever you need the aggregation, calculate the start and end value as shown before and put them in the variable.
If it is always the current month, you can do it on step enter.
If it depends on user input, do it when your trigger is executet (right before)…
Then your contition to check the aggregation is sufficient (it will consider the time range) and there is no additional condition needed
