topic Re: restricting drill down for quartile field in QlikView

restricting drill down for quartile field

kmstephenson — Mon, 25 Aug 2014 18:04:56 GMT

Hi All,

I have a simple bar chart in which I want to display hospitalizations per patient year on the y-axis and hcc quartile on the x-axis. I am having difficulty with the dimension for this chart. My input dataset has the field hcc_c_v22_commun (populated with hcc score). This field can be used in a calculated dimension to split hccs into quartiles by using the following code:

=IF(hcc_c_v22_commun<=Fractile(TOTAL hcc_c_v22_commun,.25),1,
IF(hcc_c_v22_commun<=Fractile(TOTAL hcc_c_v22_commun,.5),2,
IF(hcc_c_v22_commun<=Fractile(TOTAL hcc_c_v22_commun,.75),3,4)))

Using this code as the calculated dimension produces the desired results if I do not drill into the chart. However, if I want to select only the population with an hcc quartile of 4, selecting the bar with an hcc quartile of 4 drills down and displays 4 more bars (splits the quartile 4 bar into 4 quartiles). Is there any way to prevent this from happening? When I select any of the four quartile bars (1,2,3, or 4) for any of my other selections, I want only that quartile bar to display- I don't want the quartile bar to continue to be split into 4 quartiles.

Thank you!

Re: restricting drill down for quartile field

JonnyPoole — Mon, 25 Aug 2014 18:37:10 GMT

Is it a drill group ? if it is you can calculate it differently depending on the level

you can do an if( getcurrentfield ([GroupName]) = 'level1', <your expression> , <other expression>)

or you can use getselectedcount and getpossiblecount on the population field to manage the same

Re: restricting drill down for quartile field

kmstephenson — Mon, 25 Aug 2014 19:21:34 GMT

The code I included above is the calculated dimension. I want the quartile to be calculated using the above fractile code only if the hcc_c_v22_commun is not currently selected. If the user selects one of the quartile bars, I do not want that bar to be split into 4 more quartiles. Does this make sense? The problem is that the code I have listed above for the calculated dimension continues to split the hcc scores into quartiles even if I have an hcc quartile already selected. If I select quartile 4, I want only the bar for quartile 4 to appear. Does this code you provided above help solve this?

Re: restricting drill down for quartile field

JonnyPoole — Mon, 25 Aug 2014 19:34:23 GMT

Ok. If you add a SET MODIFIER to exclude the selection on the hcc_c_v22_commun field it will show the full total of the bar you select and hide the rest. See if this alternative calculated dimension expression works as is for you. If not, can you share your qvw ?

IF(hcc_c_v22_commun<=Fractile( {$<hcc_c_v22_commun=>} TOTAL hcc_c_v22_commun,.25),1,

IF(hcc_c_v22_commun<=Fractile( {$<hcc_c_v22_commun=>} TOTAL hcc_c_v22_commun,.5),2,

IF(hcc_c_v22_commun<=Fractile( {$<hcc_c_v22_commun=>} TOTAL hcc_c_v22_commun,.75),3,4)))

Re: restricting drill down for quartile field

kmstephenson — Mon, 25 Aug 2014 19:38:36 GMT

That is exactly what I wanted! Thank you!

Re: restricting drill down for quartile field

kmstephenson — Thu, 09 Oct 2014 16:00:22 GMT

just so that I make sure I'm understanding set analysis and fractile function, if replace {$} with {1} in the above script, I would be splitting the hccs for the total population (not accounting for my selections) into quartiles, and then any time I make a selection, my data will update to what group of patients from the selection fall into each of those originally set quartiles?

Re: restricting drill down for quartile field

JonnyPoole — Thu, 09 Oct 2014 16:05:51 GMT

I THINK so.. and i think because the answer is 'yes' in theory but i haven't looked at this QVW in a while and unsure if there is anything that would prevent that from not being the case. can't think of anything but its been awhile

To elaborate.... the {$} will apply the operations on all the records that are within the user's selection state whereas {1} will apply the operation on all records in the entire data model regardless of selections.

The SET statment however also had a modifier which disregarded any selection on hcc_c_v22_commun.

{$<hcc_c_v22_commun=>}

This modifier is completely moot with {1} so you wouldn't need it... every selection is already disregarded.