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

[sunny_talwar]

Which part of the expression isn't available?

this? -> Sum([number of expected diagnosis])

[#3]

Sunny,

The number of expected diagnosis is available, but there is no actual data for month 5. So the formula doesn't include the weight for that month when I look at the results for e.g. the first half-year... Hence the target sum is too low.

[sunny_talwar]

Would it be possible to share a sample to show the issue? I am having hard time picturing what might be going wrong here

[#5]

Sunny, here you go.

There is only one diagnosis (DRG E69E) left in this sample. The expected patients per year is 25. So the formula tells QV to divide 25 into 12 months and multipliy it by the weight of the selected months.

So, when I select month 1, it shows me the target patients for month 1 (2 patients).

When I select month 1 and 2, it accumulates the weights of month 1 and 2 (4 patients).

The problem is that for this DRG there is no actual data for month 5. When I select month 1-6, the formula ignores the weight of month 5 and the target gets too low. In this example the number of target patients is 10 when it has to be 12.

Can I somehow tell QV to include all selected months for the target patients, even if there weren't any patients in month 5? I tried with TOTAL but failed.... Thank you for your time & effort.

[sunny_talwar]

How about using an expression such as this:

Sum([Fallzahl (Anzahl der DRG)])/12

*

Pick(Match(MaxString(EntlMonatOhneJahr), '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'),

0.784, 1.801, 2.908, 3.915, 4.904, 5.918, 6.913, 7.853, 8.842, 9.902, 10.935, 11.999)

[#7]

Morning Sunny,

it works whenever I select a row of successive months starting with month 1. But when I select let's say only month 3 it gives me an accumulated target of 6 instead of 2 (which would be my expected number of patients for month 3).

I know it's knotty but we're already on the right page

[sunny_talwar]

Would you be able to provide me few expected outputs with and without selections?

[#9]

Hi,

here are the different results between old and new formula (no selection):

12.3 is the correct output

selection month 3:

2.3 is correct

selection month 4:

2.1 is correct

selection moth 3+4:

4.4 is correct

Hope it will help you.

[sunny_talwar]

I guess you missed an important scenario when somebody selects 03 and 04 and 05 and 06. Rest other are easy to configure based on the condition if(GetSelectedCount(EntlMonatOhneJahr) .....), but what I additionally would want to know is how the expected output for 3, 4, 5, 6 will look like