Missing Values, once again...

[#1]

Hi,

may anyone can help me out with this problem:

I got a table that includes diagnosis data for every patient (one patient per line).

Now I wanna do a target-performance-comparsion. (The target data comes form a different table.) The tricky thing is that there is a weighting per month for the target data but not every diagnosis is performed in ever month, so the data gets lost in the accumulation of the weight.

Here is what the target formula looks like:

sum([number of expected diagnosis])/12*sum(Distinct if(month = '01', 0.7, if(month = '02', 0.8, if(month = '03', 1.1, if(month = '04', 1, if(month = '05', 0.9, if(month = '06', 1.2, if(month = '07', 0.9, if(month = '08', 0.9, if(month = '09', 0.9, if(month = '10', 1, if(month = '11', 1, if(month = '12', 0.8)))))))))))))

It works as far as there is a value for every month.

Maybe you guys got an idea?

Thanks,

Markus

[#2]

Sorry. Here's the selection:

I selcted months 3+4+5+6 but since there are no values for month 5 the output is incomplete. The correct accumulated output for the months 3+4+5+6 is 8.6.

Hope it helps.

[sunny_talwar]

I guess the best solution for you would to add the missing months. I can't think of making this possible. May be someone else can offer a better advice.

[vinieme12]

why don't you add the monthweights to the load script as a field? instead of multiple IF's!

your expression should then be as below

sum([number of expected diagnosis])/12* sum(monthweights) )

[vinieme12]

do a left join of your monthweights to your months field

[vinieme12 · Accepted solution]

See attached

[#7]

This pretty much works out!

What a ride Thank you both!!