How to ignore/change selection when summing

[#1]

I am looking to create a bar chart showing the sum of paid for a certain payment category (selected from a field list) divided by the sum of loss, with calendar year on the y-axis as shown below.  My chart works perfectly, I can select the payment type and the it gives me accurate outputs.  The problem is, if I keep everything the same as shown in the picture, but then also select a state such as Pennsylvania, the output is not correct.  I am thinking it has something to do with the formula I used for the expression:

=Sum(CY_Payment_Amount)/Sum({$<Payment_Type={'Loss'}>}CY_Payment_Amount)

It seems like the formula is ignoring my selection for state.  What would be a dynamic way of handling this ? I may want to filter on more additional things than just state.

[prabhu0505]

=Sum(CY_Payment_Amount)/Sum({$<Payment_Type={'Loss'}>}CY_Payment_Amount)

Above expression will not exclude the state selection. If you want to exclude it something like below has to be written.

Sum({$<Payment_Type={'Loss'}, State=>}CY_Payment_Amount)

[#3]

If I change my chart expression to

Sum({$<Payment_Type={'Loss'}>}CY_Payment_Amount)

The result is the sum of loss by calendar year for all states, even if I have Pennsylvania selected. So it does appear to be ignoring the state selection.

[cjohnson]

Can you give more details regarding why the output isn't correct?

Do the values not change when you select State = Pennsylvania? I don't see any issues with the expression that you currently have.

Can you also tell us a little more about your model? Are Payment Type and State on the same tables? How are they connected? If you put a list box for State on your dashboard and select Pennsylvania - is it connected to all Payment Types?

[Anonymous]

In theory it should work as you expect when you select the state of Pennsylvania it should not be ignored.  I can only guess that there may be some quirk in your data model and / or data causing this.

[#6]

The formula below gives me the desired output.

Sum({$<Payment_Type={'Loss'},ACCIDENT_STATE={'PA'}>}CY_Payment_Amount)

If I use this formula:   Sum({$<Payment_Type={'Loss'}>}CY_Payment_Amount)

Then select PA in a list box as the accident state then it gives me the data as though accident state is being ignored. If I then remove the PA selection there is no change.

[#7]

Not sure why the formula wasn't working, but I figured out a temporary fix by creating a variable which is equal to the selected state.

=count({$<Payment_Type={'Loss'}

$(=

if(getselectedcount(ACCIDENT_STATE)=0,'',

', ACCIDENT_STATE={' & chr(39) & Selected_State & chr(39) & '}')

)

>} distinct CLAIM_NUMBER)

[simenkg · Accepted solution]

Are you using alternate states?

The  $ in your expression tells the formula to use the default(inherited) state. If the selections on state are made in another QlikView-State, then the formula would ignore it.

[#9]

Yes I am using multiple states!  I didn't realize that would affect it. Thanks!