Help with formula AND & OR case when not working as expected.

[CapNcook]

Could someone help me troubleshoot a formula I've written for a calculated field? I have a Combined Chart and Report Datapage. The chart is a stacked bar graph, and I am using 2 calculated fields for the values on the chart. In the calculated fields, I want to create a number value for the values in two text (255) fields. So if both fields say the same value, the number in the calculated field should be 2; if only one of the fields has the correct value, the number in the calculated field should be 1.

This is what I've written:
CASE
WHEN [@field:Publications_Table_Slide_Status] = 'Requested' OR [@field:Publications_Table_Summary_Status] = 'Requested'
THEN '1'
WHEN [@field:Publications_Table_Slide_Status] = 'Requested' AND [@field:Publications_Table_Summary_Status] = 'Requested'
THEN '2'
ELSE '0'
END

The calculation shows 1 in all cases, no matter what value the fields have in them

[ianGPT]

Your formula seems almost correct, but there's a small logical issue in the order of your conditions. Since the first condition checks for either of the fields being 'Requested', the second condition will never be reached. You should reorder the conditions to check for the case where both fields are 'Requested' first, and then check for the case where only one of the fields is 'Requested'. Here's the corrected version of your formula:

CASE
WHEN [@field:Publications_Table_Slide_Status] = 'Requested' AND [@field:Publications_Table_Summary_Status] = 'Requested'
THEN '2'
WHEN [@field:Publications_Table_Slide_Status] = 'Requested' OR [@field:Publications_Table_Summary_Status] = 'Requested'
THEN '1'
ELSE '0'
END

This way, if both fields are 'Requested', the first condition will be satisfied and the value will be '2'. If only one of the fields is 'Requested', the second condition will be satisfied and the value will be '1'. If neither condition is satisfied, the 'ELSE' branch will result in the value '0'.

Hope this helps!

[kpcollier]

8 minutes ago, CapNcook said:

Could someone help me troubleshoot a formula I've written for a calculated field?

Just for future reference, questions like these can be quickly answered by ChatGPT, Bard, Bing, or other AI tools.

I just put the question as you wrote it in ChatGPT and it gave me @ianGPT's answer in just a few seconds. It is very helpful and can be used for many coding questions.

[Tubby]

For anyone who stumbles upon a similar issue as this one. Basically, the issue is with the order of the conditions because of how SQL (which is used for calculated fields/values) executes scripts. It executes from top to bottom. So if it meets the first CASE, then it doesn't bother checking the second CASE. Quite tricky to set up in some cases, I must say.