Combining DSum and IIf, within a query, in a single expression.

[GratefulSteve]

I am running a query, which supports a yearly line graph broken into months. I am tracking, on a monthly basis, New Patients. One field tracks our Goal for New Patients (NPGoal). Another field tracks Actual New Patients (NPActual).



I am using DSum to create an aggragate from month to month, for both fields.

New Patient Goals are set for each month, at the beginning of the year. We enter the Actual New Patients during each month.

However, I don't want to show the current total of NPActual through March, throughout the rest of the year. It creates an odd looking graph, with the Goal line climbing and the Actual line going flat for the rest of the year, at the latest month's level.

This said, I am having trouble combining IIf with Dsum.

I would like the months that have zero NPActual to flat line at zero, until data (NPActual) has been entered for that month.

In my query, I can successfully generate ongoing monthly totals, setting Total to Expression, with:

Actual: DSum("NPActual","Calendar","DatePart('m', [DateTime])<=" & [Month] & " And DatePart('yyyy', [DateTime])=" & [YearName] & "")

In my query, I can successfully replace a running total with zeros, setting Total to Sum, with:

Actual: IIf([NPActual]=0,0,[Calendar]![NPActual])

This is my latest clumsy attempt to combine them:

Actual: DSum("NPActual","Calendar","DatePart('m', [DateTime])<=" & [Month] & " And DatePart('yyyy', [DateTime])=" & [YearName] & "")+IIf([NPActual]=0,0,[Calendar]![NPActual])


I would be extremely appreciative if someone can help me bring this in for a landing.

Thank you very much.

[InsuranceGuy]

I am running a query, which supports a yearly line graph broken into months. I am tracking, on a monthly basis, New Patients. One field tracks our Goal for New Patients (NPGoal). Another field tracks Actual New Patients (NPActual).

I am using DSum to create an aggragate from month to month, for both fields.

New Patient Goals are set for each month, at the beginning of the year. We enter the Actual New Patients during each month.

However, I don't want to show the current total of NPActual through March, throughout the rest of the year. It creates an odd looking graph, with the Goal line climbing and the Actual line going flat for the rest of the year, at the latest month's level.

This said, I am having trouble combining IIf with Dsum.

I would like the months that have zero NPActual to flat line at zero, until data (NPActual) has been entered for that month.

In my query, I can successfully generate ongoing monthly totals, setting Total to Expression, with:

Actual: DSum("NPActual","Calendar","DatePart('m', [DateTime])<=" & [Month] & " And DatePart('yyyy', [DateTime])=" & [YearName] & "")

In my query, I can successfully replace a running total with zeros, setting Total to Sum, with:

Actual: IIf([NPActual]=0,0,[Calendar]![NPActual])

This is my latest clumsy attempt to combine them:

Actual: DSum("NPActual","Calendar","DatePart('m', [DateTime])<=" & [Month] & " And DatePart('yyyy', [DateTime])=" & [YearName] & "")+IIf([NPActual]=0,0,[Calendar]![NPActual])

Please provide your actual query in addition to the criteria you're using. DSUM is probably not your best option.

Jeff

[GratefulSteve]

Please provide your actual query in addition to the criteria you're using. DSUM is probably not your best option.

Jeff

Thank you for your reply, Jeff.

SELECT Format([DateTime],"mmm") AS MonthName, DSum("NPGoal","Calendar","DatePart('m', [DateTime])<=" & [Month] & " And DatePart('yyyy', [DateTime])=" & [YearName] & "") AS Goal, DSum("NPActual","Calendar","DatePart('m', [DateTime])<=" & [Month] & " And DatePart('yyyy', [DateTime])=" & [YearName] & "") AS Actual, Month([DateTime]) AS [Month], Format([DateTime],"yyyy") AS YearName
FROM Calendar
GROUP BY Format([DateTime],"mmm"), Month([DateTime]), Format([DateTime],"yyyy")
HAVING (((Format([DateTime],"yyyy"))="2016"))
ORDER BY Month([DateTime]);

FWIW, I'm also attaching the design view.

Thank you, again...

[InsuranceGuy]

That helps. What do you want to happen when the goal month is not yet here? Do you want no value to be displayed in Goal, no value to be displayed in actual, or do you want the months not even to appear at all? A table showing your desired results would be helpful.

Jeff

[GratefulSteve]

That helps. What do you want to happen when the goal month is not yet here? Do you want no value to be displayed in Goal, no value to be displayed in actual, or do you want the months not even to appear at all? A table showing your desired results would be helpful.

Jeff

Thank you, once again, for your reply Jeff.

I am attaching an image of the current graph. For months in the future, I would like the Actual line to drop to zero on the X axis until at least one New Patient has been entered for the current month. This New patient would be added to the ongoing total.

Using the attached graph, all months for the Actual line, from April forward, would drop to the x-axis. Once a New Patient had been added for April, the Actual line would rise to 85 and climb for that month. All other future months would remain at zero until each month began.

Hope I'm explaining this clearly.

Thank you, again,

Steve

[InsuranceGuy]

I think we're closer.

When you say you want the query to return zero, do you mean a) return a zero increment to the running total; or, b) return an actual zero in the month's results (replacing the running total).

Thanks,

Jeff

[GratefulSteve]

I think we're closer.

When you say you want the query to return zero, do you mean a) return a zero increment to the running total; or, b) return an actual zero in the month's results (replacing the running total).

Thanks,

Jeff

Hi Jeff,

Thank you for your patience. :-)

The latter. That's why I was trying to use the IIf. My intention was to display zero in any future month until the first of the month. Then, when the first entry under Actual was entered, the graph would pick right up with the previous months total plus the new Actual entry.

Thanks again,

Steve

[InsuranceGuy]

Hi Jeff,

Thank you for your patience. :-)

The latter. That's why I was trying to use the IIf. My intention was to display zero in any future month until the first of the month. Then, when the first entry under Actual was entered, the graph would pick right up with the previous months total plus the new Actual entry.

Thanks again,

Steve

Try this--

SELECT Format([DateTime], "mmm") AS MonthName     ,iif(sum([NPActual])>0,DSum("NPGoal", "Calendar", "DatePart('m', [DateTime])<=" & [Month] & " And DatePart('yyyy', [DateTime])=" & [YearName] & ""),0) AS Goal
     ,iif(sum([NPActual])>0,DSum("NPActual", "Calendar", "DatePart('m', [DateTime])<=" & [Month] & " And DatePart('yyyy', [DateTime])=" & [YearName] & ""),0) AS Actual
     ,Month([DateTime]) AS [Month]
     ,Format([DateTime], "yyyy") AS YearName
FROM Calendar
GROUP BY Format([DateTime], "mmm")
     ,Month([DateTime])
     ,Format([DateTime], "yyyy")
HAVING (((Format([DateTime], "yyyy")) = "2016"))
ORDER BY Month([DateTime]);

[GratefulSteve]

Hi Jerry,

That was it!

You wrapped the DSum inside of the IIf. I was trying to wrap the IIF inside of the DSum.

You've taught me something, which is always a good thing.

Thank you very much, Jerry!

Take care,

Steve

[InsuranceGuy]

Happy to help. Cheers,

Jeff