Set Analysis - Sum

[#1]

Hello,

I created a text object and want to show the "Total Late Cases" number.

So I add this code into "General -- > Text" :

='Total Late Cases: ' &  sum( { $ <(DUE_DATE - DATE_SUBMITTED) = {"<0"} > } [Submission Count])

I am expecting above code can return the sum of [Submission Count] which will be the numeric result less than zero. But it show error. I am not sure where it has issues with.  Can you point out if you find any problems?

Thanks,

Becky

[swuehl]

You can only use field names left of the equal sign of a set analysis field modifier.

You are using an expression (subtracting two fields).

Try

='Total Late Cases: ' &  sum( If [DUE_DATE] - [DATE_SUBMITTED] <0,[Submission Count]))

If you need to do a row based comparison, then you can only use set analysis if you find a key field that is granular enough for the comparison to be unambiguous, maybe a CaseID.

='Total Late Cases: ' &  sum( {<CaseID = {"=[DUE_DATE] < [DATE_SUBMITTED]"}>} [Submission Count])

[#3]

Thank you! It works.

I am not sure about "I can only use field names left of the equal sign". Does that mean if there are more than one field involved, then I will need to use expression or the second solution you provided?

Thanks & have a great weekend.

Becky

[#4]

The more interesting thing is I tried to use two of your solutions. But the results are totally different. The result from the first solution returned 8 times greater than the second solution.

What could be the reason?

[swuehl]

To answer this, you would need to give some more information how the fields involved are related / linked in your data model.

Coudl you give a short overview of how your data model looks like?

[#6]

There are two tables in my data model layer. They are connected by Case_ID but have different data fields. There is no join or cancatenate in the data model.

I used Case year, quarter and month from Table A, and Du Date, Date Submitted from Table B. Try to get "Late Days", how many late cases, total cases.

I am not sure if this is enough information for you to think through? Please let me know your thoughts.

Thanks,

Becky

[swuehl]

I am not sure about "I can only use field names left of the equal sign". Does that mean if there are more than one field involved, then I will need to use expression or the second solution you provided?

You probably know that you can make selections only on field values, so even if you create e.g. a more complex field expression in a list box, the selections are made on field values in your data model (you see the selections e.g. when looking at the Current Selections Box).

Set Analysis is basically the developers tool to create selections to be used in the context of an aggregation function.

Still, you can only use selections on field values. Hence only field names allowed on the left of the equal sign (which is not a comparison operator, but more like an assignment operator).

A Primer on Set Analysis