topic Re: Missing Values, once again... in QlikView

Missing Values, once again...

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Missing Values, once again...

sunny_talwar — Wed, 10 Aug 2016 08:41:46 GMT

Which part of the expression isn't available?

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

Re: Missing Values, once again...

Wed, 10 Aug 2016 08:58:17 GMT

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.

Re: Missing Values, once again...

sunny_talwar — Wed, 10 Aug 2016 10:38:06 GMT

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

Re: Missing Values, once again...

Wed, 10 Aug 2016 12:46:38 GMT

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.

Re: Missing Values, once again...

sunny_talwar — Wed, 10 Aug 2016 16:10:58 GMT

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)

Re: Missing Values, once again...

Thu, 11 Aug 2016 06:36:51 GMT

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

Re: Missing Values, once again...

sunny_talwar — Thu, 11 Aug 2016 09:05:25 GMT

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

Re: Missing Values, once again...

Thu, 11 Aug 2016 11:23:13 GMT

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.

Re: Missing Values, once again...

sunny_talwar — Thu, 11 Aug 2016 12:46:29 GMT

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

Re: Missing Values, once again...

Fri, 12 Aug 2016 07:29:40 GMT

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.

Re: Missing Values, once again...

sunny_talwar — Fri, 12 Aug 2016 09:53:08 GMT

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.

Re: Missing Values, once again...

vinieme12 — Fri, 12 Aug 2016 10:02:59 GMT

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) )

Re: Missing Values, once again...

vinieme12 — Fri, 12 Aug 2016 10:03:51 GMT

do a left join of your monthweights to your months field

Re: Missing Values, once again...

vinieme12 — Sat, 13 Aug 2016 11:57:05 GMT

See attached

Re: Missing Values, once again...

Wed, 17 Aug 2016 06:03:46 GMT

This pretty much works out!

What a ride Thank you both!!