Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
michael84
Contributor II
Contributor II

Set Analasys

Hello

I got the following bar chart with the following formula:

Unbenannt.png

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

10 Replies
swuehl
MVP
MVP

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

michael84
Contributor II
Contributor II
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
swuehl
MVP
MVP

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?

michael84
Contributor II
Contributor II
Author

Hi Jonathan

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

michael84
Contributor II
Contributor II
Author

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

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)

jonathandienst
Partner - Champion III
Partner - Champion III

Michael

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

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
michael84
Contributor II
Contributor II
Author

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

swuehl
MVP
MVP

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.