I have a user interface which includes a date picker. I have created extra fields to breakdown the date entered into Week of Year, Week of Quarter, Quarter and Year. When the date picker has nothing selected I have used coalesce to replace the Null values in these fields with a string. Because I used a formula to get the Week of Qtr, coalesce doesn't seem to work, I have possibly made a syntax error.
This is my formula to get the Week of the Quarter:
{../weekLabel.props.text}-(13*({../qtrLabel.props.text}-1))
I have tried this to handle Null values, without success:
coalesce({../weekLabel.props.text}-(13*({../qtrLabel.props.text}-1)), "WoQ")
Perhaps a logical expression would work better, something like:
if ( {../weekLabel.props.text} =! Null,
do calculation, " ")
Since I started typing this question and playing around with it, the Week of Quarter is now returning the 13 from my formula whenever the date is blank. coalesce now also returns 13 
TLDR: I would like to calculate Week of Quarter when a date is selected from the date picker, or return a string if no date is selected.
The issue with your coalesce expression is that you're not using it on something that can be null. You're using it on an expression that USES something that can be null.
Also, if you're checking for nulls in an expression, use isNull({var}), not {var] = null
There's another option that you can consider: try(expr, fallback). This will try expr, and if it fails, return fallback.
With all this in mind:
Using coalesce here is complicated. Because you'd have to coalesce the date value, which would allow you to fallback to a value if it's null, but then there's still the operation.
if is a solution:
if (isNull({../weekLabel.props.text}),
"WoQ",
{../weekLabel.props.text} - (13*({../qtrLabel.props.text}-1)
)
try is another one:
try({../weekLabel.props.text}-(13*({../qtrLabel.props.text}-1)), "WoQ")
Things to note about the whole thing: Why are you using labels values ? If you have input components, use those instead as your data source.
What's in qtrLabel ? I expect it's the number of the quarter, right ?
1 Like
@pascal.fragnoud yeah qtrLabel is the number of the quarter. Ok, I'll try reference the input date instead of the labels, I thought the labels would suit because I want the user to be able to see the breakdown of the date on the UI without them being able to edit it. You're right though it's probably not necessary. I will try implement your solutions, thank you very much
You can use labels to show the breakdown. What I'm saying is that you shouldn't use them as data sources.
Do your calculations on custom properties, then bind your labels to those properties. This will allow you to format them however you like, without modifying the underlying values.
Also, at first glance, the formula to get the WoQ doesn't seem right.
@pascal.fragnoud I get you, ok I'll have another go at the formula, thank you
I think this looks like what you want:
view's json
{
"custom": {},
"params": {},
"props": {
"defaultSize": {
"height": 425,
"width": 646
}
},
"root": {
"children": [
{
"meta": {
"name": "DateTimeInput"
},
"position": {
"basis": "32px"
},
"props": {
"formattedValue": "Apr 20, 2023 12:00 PM",
"value": {
"$": [
"ts",
0,
1697536910611
],
"$ts": 1681984800000
}
},
"type": "ia.input.date-time-input"
},
{
"meta": {
"name": "month"
},
"position": {
"basis": "32px"
},
"propConfig": {
"props.text": {
"binding": {
"config": {
"expression": "\"month: \" + ({parent.custom.month}+1)"
},
"type": "expr"
}
}
},
"type": "ia.display.label"
},
{
"meta": {
"name": "quarter"
},
"position": {
"basis": "32px"
},
"propConfig": {
"props.text": {
"binding": {
"config": {
"expression": "\"quarter: \" + {parent.custom.quarter}"
},
"type": "expr"
}
}
},
"type": "ia.display.label"
},
{
"meta": {
"name": "week"
},
"position": {
"basis": "32px"
},
"propConfig": {
"props.text": {
"binding": {
"config": {
"expression": "\"week: \" + {parent.custom.week}"
},
"type": "expr"
}
}
},
"type": "ia.display.label"
},
{
"meta": {
"name": "qstart"
},
"position": {
"basis": "32px"
},
"propConfig": {
"props.text": {
"binding": {
"config": {
"expression": "\"start of quarter: \" + dateFormat({parent.custom.qstart}, \u0027yyyy/MM/dd\u0027)"
},
"type": "expr"
}
}
},
"type": "ia.display.label"
},
{
"meta": {
"name": "woq"
},
"position": {
"basis": "32px"
},
"propConfig": {
"props.text": {
"binding": {
"config": {
"expression": "\"woq: \" + {parent.custom.woq}"
},
"type": "expr"
}
}
},
"type": "ia.display.label"
}
],
"meta": {
"name": "root"
},
"propConfig": {
"custom.month": {
"binding": {
"config": {
"expression": "dateExtract({./DateTimeInput.props.value}, \u0027month\u0027)"
},
"type": "expr"
}
},
"custom.qstart": {
"binding": {
"config": {
"expression": "getDate(\r\n\tdateExtract({./DateTimeInput.props.value}, \u0027year\u0027),\r\n\t{this.custom.month} - ({this.custom.month} % 3),\r\n\t1\r\n)"
},
"type": "expr"
}
},
"custom.quarter": {
"binding": {
"config": {
"expression": "round(floor(({this.custom.month}) / 3) + 1)"
},
"type": "expr"
}
},
"custom.week": {
"binding": {
"config": {
"expression": "dateExtract({./DateTimeInput.props.value}, \u0027week\u0027)"
},
"type": "expr"
}
},
"custom.woq": {
"binding": {
"config": {
"expression": "round(\r\n\tdateDiff(\r\n\t\t{this.custom.qstart},\r\n\t\t{./DateTimeInput.props.value},\r\n\t\t\u0027week\u0027\r\n\t)\r\n)"
},
"type": "expr"
}
}
},
"props": {
"direction": "column"
},
"type": "ia.container.flex"
}
}
Note that dateExtract returns 0-indexed months. Which is actually what you need to do the calculations.
The month labels adds 1 to that for display.
1 Like