topic Re: Set Analasys in QlikView

Set Analasys

michael84 — Mon, 10 Dec 2012 10:23:07 GMT

Hello

I got the following bar chart with the following formula:

My current selection is Year = 2012.

Avg

( {$<Year= {$(=only(Year))}, Diff_Terminvergabe_Untersuch={">0"}, Patientenart= {'ambulant'},

Diff_Terminvergabe_Untersuch={
"=(Avg(Diff_Terminvergabe_Untersuch)+(Stdev(Diff_Terminvergabe_Untersuch)*3))>=Diff_Terminvergabe_Untersuch"
} >} Diff_Terminvergabe_Untersuch)

The socond formula ist the same as the one above, only the expressen Year= {$(=only(Year)-1)

is supposed to show the 2011 values, but it won't as i want... In fact 2011 should look like 2012. When removing "Year= {$(=only(Year))", then it's working as long as the current selection ist 2012 and 2011. But thats not how i want it...

Thanks for your Help

Michael

Re: Set Analasys

swuehl — Mon, 10 Dec 2012 11:01:53 GMT

Please check that you have cleared all fields that the user may select in and that may interfere with your modification in Year field (i.e. are also calender fields linked to a certain year)

For example and as far as I see, you are using a Kalender_1.Kalender_Jahr field. If this is a field the user may select in, you need to clear this field:

Avg

( {$<Year= {$(=only(Year)-1)}, Diff_Terminvergabe_Untersuch={">0"}, Patientenart= {'ambulant'},

Diff_Terminvergabe_Untersuch={
"=(Avg(Diff_Terminvergabe_Untersuch)+(Stdev(Diff_Terminvergabe_Untersuch)*3))>=Diff_Terminvergabe_Untersuch"
}

,Kalender_1.Kalender_Jahr=

>} Diff_Terminvergabe_Untersuch)

Hope this helps,

Stefan

Re: Set Analasys

michael84 — Mon, 10 Dec 2012 13:42:33 GMT

Hi Stefan

The Dimension "Kalender_1.Kalender_Jahr" is the same as "year", I just renamed it, so that it is understandable for all english speaking people... Unfortunately this istn't the solution...

Michael

Re: Set Analasys

jonathandienst — Mon, 10 Dec 2012 13:51:24 GMT

Michael

I would try replacing the Only with Max, like this:

{$<Year= {$(=Max(Year))}, ....

and

{$<Year= {$(=Max(Year)-1)}, ....

Just a guess, hope this helps

Jonathan

Re: Set Analasys

swuehl — Mon, 10 Dec 2012 13:54:22 GMT

I just noticed that you set two field modier for field Diff_Terminvergabe_Untersuch.

This won't work.

Then, it might be necessary to also use a set expression in your advanced search aggregation for that field.

Diff_Terminvergabe_Untersuch={
"=(Avg({<Year = {$(=only(Year)-1)}>} Diff_Terminvergabe_Untersuch)+(Stdev({<Year = {$(=only(Year)-1)}>} Diff_Terminvergabe_Untersuch)*3))>=Diff_Terminvergabe_Untersuch"
}

Could you upload small sample that demonstrate your issue?

Re: Set Analasys

michael84 — Mon, 10 Dec 2012 14:00:51 GMT

Hi Jonathan

Just tried this one, but it didn't change a thing...

Re: Set Analasys

michael84 — Mon, 10 Dec 2012 14:11:18 GMT

I just changed it as you suggested... It changes data in the year 2011 but still, it's not as it should...

maybe any other ideas?

The formula for 2011 now looks like this (if it's max and only it's exactly the same):

Avg

( {$<year= {$(=max(year)-1)}, Diff_Terminvergabe_Untersuch={">0"}, Patientenart= {'ambulant'},

Diff_Terminvergabe_Untersuch={

"=(Avg( {<year= {$(=max(year)-1)}>} Diff_Terminvergabe_Untersuch)
+
(Stdev({<year= {$(=max(year)-1)}>}Diff_Terminvergabe_Untersuch)*3))>=Diff_Terminvergabe_Untersuch"

} >} Diff_Terminvergabe_Untersuch)

Re: Set Analasys

jonathandienst — Mon, 10 Dec 2012 14:13:38 GMT

Michael

You might need to post your model or a sample file so that this can be investigated in a little more detail.

Jonathan

Re: Set Analasys

michael84 — Mon, 10 Dec 2012 15:35:04 GMT

Here's the sample... Pleas note that the field "year" doesn't exists, instead you will use "Kalender_1.Kalender_Jahr"

Thanks for your help!

Michael

Re: Set Analasys

swuehl — Mon, 10 Dec 2012 16:02:11 GMT

Try this:

Avg( {$<Kalender_1.Kalender_Jahr= {$(=max(Kalender_1.Kalender_Jahr)-1)},

Diff_Terminvergabe_Untersuch={

"=(Avg( {<Kalender_1.Kalender_Jahr= {$(=max(Kalender_1.Kalender_Jahr)-1)}>} Diff_Terminvergabe_Untersuch)

(Stdev({<Kalender_1.Kalender_Jahr= {$(=max(Kalender_1.Kalender_Jahr)-1)}>}Diff_Terminvergabe_Untersuch)*3))

>=only({<Kalender_1.Kalender_Jahr= {$(=max(Kalender_1.Kalender_Jahr)-1)}>}Diff_Terminvergabe_Untersuch)"

} >} Diff_Terminvergabe_Untersuch)

Seems to work for me.

Re: Set Analasys

michael84 — Tue, 11 Dec 2012 08:00:38 GMT

Unbelievable you made it… Thanks a lot for your help!!!