topic Re: Set analysis with 'or' in QlikView

Set analysis with 'or'

Thu, 16 Oct 2014 08:56:17 GMT

I have a dataset in QlikView where a single case can have a number of letters that have been sent, say letter A and letter B.

The calculation i am trying to perform in set analysis is if a case has had letter A sent before or on its due date, or letter B was sent before the case was 5 days old then count the case as being compliant.

I have added the relevant flags and fields to enable me to perform the above in set analysis however, there are a handful of cases that meet both conditions, these cases are subsequently being counted twice. Is there any way i can ensure that each case is only counted once?

An example of my set analysis is (I have simplified this as there is a lot of conditions in that set analysis that aren't relevant to this question - i am more looking for advice on how to approach the issue that the code to make it work):

num(count(DISTINCT{<
ACTION_DESCRIPTION={"letter A"},
ACTION_OUTCOME_DESCRIPTION={Complete},
OUTCOME_DATE={"<=$(=date(vCurrentPeriodEnd))>=$(=date(vCurrentPeriodStart))"},
>}Case_Dimension_ID)

+

count(DISTINCT{<
ACTION_DESCRIPTION={"Letter B"},
OUTCOME_DATE={"<=$(=date(vCurrentPeriodEnd))>=$(=date(vCurrentPeriodStart))"},
ACTION_OUTCOME_DESCRIPTION={Complete},
CASE_AGE_CALC={"<=5"},
>}Case_Dimension_ID),0.0)

vCurrentPeriodStart and vCurrentPeriodEnd are just variables that define the start and end of the current reporting period.

Any advice/thoughts would be greatly appreciated

Re: Set analysis with 'or'

kuba_michalik — Sat, 18 Oct 2014 07:51:08 GMT

You can sum both sets before you calculate. That way, the records that would appear in both sets are counted only once in the resulting sum of sets.

Here's your expression changed to work this way:

num(count(DISTINCT
{
<ACTION_DESCRIPTION={"letter A"},
ACTION_OUTCOME_DESCRIPTION={Complete},
OUTCOME_DATE={"<=$(=date(vCurrentPeriodEnd))>=$(=date(vCurrentPeriodStart))"}>
+
<ACTION_DESCRIPTION={"Letter B"},
OUTCOME_DATE={"<=$(=date(vCurrentPeriodEnd))>=$(=date(vCurrentPeriodStart))"},
ACTION_OUTCOME_DESCRIPTION={Complete},
CASE_AGE_CALC={"<=5"}>
}
Case_Dimension_ID),0.0)

Re: Set analysis with 'or'

Thu, 13 Nov 2014 14:25:45 GMT

Hi Jakub,

Apologies for the delayed response. Your solution works perfectly, i didnt realise that you could write set analysis in this way so thank you for showing me.