topic Re: Aggregation & Indirect Set Analysis in QlikView

Aggregation & Indirect Set Analysis

Anonymous — Wed, 25 Oct 2017 15:53:34 GMT

Hi,

I am trying to calculate the number of calls in a month where the caller called for an hour and got unavailable messages and was never answered in that hour.

I have used the following as the expression:

sum(aggr(sum({$<ContactOutcome={Unavailable}

,CallerId = e({$<ContactOutcome={Answered}>}

CallerId)>} ContactCounter)

,CallerId, Hour, ContactDate))

and Year-Month as the dimension.

The ContactCounter is a value of '1' for each record (each record is a separate call).

I am aware this is not calculating correctly as the CallerId exclusion in the indirect set analysis appears to be performed on the whole set before the aggregation.

How would I change this expression so that the indirect set analysis is performed on the aggregation?

I am also trying to use a similar process to find the average number of calls over a month which resulted in an unavailable outcome per caller made in a given hour where the caller had one or more answered calls for that same hour.

Re: Aggregation & Indirect Set Analysis

vkish16161 — Wed, 25 Oct 2017 15:58:05 GMT

Can you please send sample data?

I don't think element functions will work here.

Re: Aggregation & Indirect Set Analysis

Anonymous — Wed, 25 Oct 2017 16:18:32 GMT

Hi Vishnu,

Does the attachment illustrate enough, or do you require a larger set of data?

Re: Aggregation & Indirect Set Analysis

vkish16161 — Wed, 25 Oct 2017 17:22:25 GMT

Try this please:

Count({<Hour={">1"},ContactOutcome={'Answered'}>}ContactCounter)

Re: Aggregation & Indirect Set Analysis

sunny_talwar — Wed, 25 Oct 2017 23:56:11 GMT

What is the expected output from this dataset you have provided?

Re: Aggregation & Indirect Set Analysis

Anonymous — Thu, 26 Oct 2017 08:14:58 GMT

Hi Vishnu,

Thanks, but that wouldn't work - Hour is hour of the day 0-23.

Re: Aggregation & Indirect Set Analysis

Anonymous — Thu, 26 Oct 2017 08:18:07 GMT

Hi Sunny,

It would be 1 as CallerId "D" is the only caller to have an Unavailable call and no Answered call in the same hour.

Re: Aggregation & Indirect Set Analysis

sunny_talwar — Thu, 26 Oct 2017 08:34:55 GMT

I would create a new field in the script like this

LOAD AutoNumber(CallerId&MonthName(ContactDate)) as Key,

CallerId,

Hour,

ContactOutcome,

ContactCounter,

Date(MonthStart(ContactDate)) as MonthYear

FROM ....

and then try this

Sum({<Hour = {"$(='>=' & MakeTime(1))"}, Key = e({<ContactOutcome = {'Answered'}>})>}ContactCounter)

This should give you a 1 against the month of August or Apr (Not sure if your date is MM/DD/YYYY or DD/MM/YYYY