Best way to filter charts based on user inputs

[#1]

I am brand new to QlikView, so hopefully I'll be able to describe what I want to do.

I have several tables with metrics in them.  In addition to the metrics, each of the tables also have related columns for each metric that show the quartile they fall into.  For example:

Table 1 relates to service A and some of the fields are:

Profit Center #     Profit Center Name     Total Expense / Sq Ft     Total Expense / Sq Ft Quartile    Total Expense / Population     Total Expense / Population Quartile

1234                    Dept A                           $100.50                           75th                                             $5.92                                        50th

1235                    Dept B                           $75.00                             25th                                              $10.50                                     75th 

Table 2 relates to a different service and has different metrics, but also has similar columns for the quartiles.

All of the tables use the 25th, 50th, and 75th values in the various quartile fields.

I want to create some stacked bar charts each of the metrics.  I want the charts to show 12 months of data.  Each of the stacked bars would be color coded to show the percent of accounts in the 25th, 50th, and 75th quartiles.

I want the users to be able to be able to select quartiles (25th, 50th, 75th) from a single list box (or better yet clicking on the colored sections of the bar charts) and have the quartile applied to all of the charts for the different metrics without having to create a list box for each of the individual quartile columns.  The end goal is that I want users to be able to see which profit centers are included in a specific quartile(s) for a metric their choice.  For instance, I want them to be able to select the 75th percentile and be shown only a list of profit centers in the 75th for the Total Expense / Sq Ft metric so that they can see the data that is driving the results in the graph without having to create a separate straight table for each individual metic.

What is the best way to do this?

I hope this makes sense!

[stevelord · Accepted solution]

First maybe create a Quartlie field using dual if statements.  If something is already putting things into the correct Quartile, you'd just reference the Quartile.  Otherwise you'd put the formulas that define each quartile into the dual if statement.  So

If(Quartile=25th, Dual('25th Quartile',1),

If(Quartile=50th, Dual('50th Quartile',2),

If(Quartile=75th, Dual('75th Quartile',3)))) as Quartile

Or you can replace 'Quartile=nth' with whatever formula tells you which quartile something is in.  If this is the case, it may get pretty complex and probably safer to write that expression into the chart as a calculated dimension referencing values in whichever fields and their totals to generate the profit center rankings.  Thinking about it, I might do one script to get the profit centers ranked on a straight table, then export that to a separate table and manually add and populate a Quartile column from that.  (Gysbert or someone smart like that might know how to rank then group things in the script itself.)  Then load that table in and create the stratified Quartile field above.

Either way, the only dimension on your bar chart might be Quartile.  Your expression would be something like =if(Quartile, Count(ProfitCenter#))

You might use a piece of set analysis to get both kinds of Quartile on there

=if({State1}QuartileESF, Count(ProfitCenter#))

=if({State2}QuartileEPop, Count(ProfitCenter#))

This would give a breakout of how many profit centers are in each Quartile.  Personally I'd prefer a couple of pie charts with one focusing on one kind of metric's dimension and the other on the other- because the pie charts can also be rigged to put counts and relative values out on the legend where they might be easier to read.

For the actual list of profit centers in a given quartile, a straight table might be easier to do and probably easier to read as well.  You could try adding profit centers to the bar chart somehow if they want to see bars, but it may get crowded.

[#3]

Thanks for your help! I used the set analysis and setup quartiles like you suggested, and it worked!

[stevelord]

Thanks for remembering the correct answer credit too.  I'm glad some caveat didn't put a monkey wrench in the whole thing.