topic Re: How to ignore/change selection when summing in QlikView

How to ignore/change selection when summing

Thu, 24 Sep 2015 19:30:17 GMT

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.

Re: How to ignore/change selection when summing

prabhu0505 — Thu, 24 Sep 2015 19:46:18 GMT

=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)

Re: How to ignore/change selection when summing

Thu, 24 Sep 2015 19:56:09 GMT

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.

Re: How to ignore/change selection when summing

cjohnson — Thu, 24 Sep 2015 20:12:05 GMT

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?

Re: How to ignore/change selection when summing

Anonymous — Thu, 24 Sep 2015 20:14:28 GMT

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.

Re: How to ignore/change selection when summing

Thu, 24 Sep 2015 20:29:44 GMT

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.

Re: How to ignore/change selection when summing

Thu, 24 Sep 2015 21:33:29 GMT

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)

Re: How to ignore/change selection when summing

simenkg — Thu, 24 Sep 2015 22:42:38 GMT

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.

Re: How to ignore/change selection when summing

Fri, 25 Sep 2015 12:58:50 GMT

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