How does QlikView handle multiple selections in one field?

[#1]

I have been experiencing the following issue:

My QV report has a field in a multi-box called "Business Division". One of the requirements is that the report needs to count the records that are not new but have changed value to match the selected Business Division. In the table we have a field which contains the previous month's value and the current month's value. If the current month's value matches the selected value and the previous month's doesn't we want a count of those records. This works fine for a single selection.

For multiple selections we are experiencing a problem. What happens when a business division value changes from one selected value to another selected value? We don't want to count these records because they aren't new to the selected divisions. This is how QlikvView appears to be handling this:

If you filter on a Business Division value of ‘A’, this would qualify as a record we want to count:

Business Division = ‘A’

Prev Business Division = ‘B’

If you filter on both ‘A’ and ‘B’ those values should not be counted, but it appears that Qlikview checks ‘A’ first without considering the ‘B’ selection so it is being counted. It then seems to check 'B' without considering 'A'. 

This is the expression that I'm using:

=count(

{

$<BUSINESS_DIVISION_D=BUSINESS_DIVISION,P_BUSINESS_DIVISION_D={'*'}-BUSINESS_DIVISION>

}

distinct MATTER_ID))

Does anyone know if this Is this how QlikView is supposed to handle this?

[swuehl · Accepted solution]

I can't reproduce your isse.

But I've needed to make some assumptions, because you haven't explained your data model, and set analysis is pretty much sensitive to your model (like most of QV).

I assumed you are using a data island for your current / previous BD records.

Check attached. If this does not match your setting, please adapt and repost.

[#3]

Thanks for helping out with this. The expression that I posted was simplified to try to isolate the problem. This seems to confirm that the problem is not what I thought it was. There are in fact 13 fields that are used to filter the data in this same way. If I were to add Business Unit to the mix the expression would look like this:

=count(

{ 

$<BUSINESS_DIVISION_D=BUSINESS_DIVISION,P_BUSINESS_DIVISION_D={'*'}-BUSINESS_DIVISION> +

$<BUSINESS_DIVISION_D=BUSINESS_UNIT,P_BUSINESS_UNIT_D={'*'}-BUSINESS_UNIT>

}

distinct MATTER_ID)

I think I will actually mark this as answered. The answer is no, QlikView doesn't handle this situation as I expected.

[#4]

I think I see the flaw in that expression actually (I didn't write this originally). What if Business Division and Business Unit both meet the condition? Then the same record would be counted twice.

[swuehl]

Well, not sure about that.

You do a distinct count of MATTER_ID, so even if a 'condition' is met twice for that ID, it will only count once (distinct).

I personally don't see a set expression's field modifier like

BUSINESS_DIVISION_D = BUSINESS_DIVISION

as a condition or comparison, rather than an assignment (where only matching values can be assigned).

I am not sure what causes your issue. If you need help, you need to provide more info about your data model and expressions, best by posting a small sample app that demonstrates your issue. Often, while building the sample app, you get to know what causes your issue by yourself...