Aggregation & Indirect Set Analysis

[Anonymous]

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.

[vkish16161]

Can you please send sample data?

I don't think element functions will work here.

[Anonymous]

Hi Vishnu,

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

[vkish16161]

Try this please:

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

[sunny_talwar]

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

[Anonymous]

Hi Vishnu,

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

[Anonymous]

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.

[sunny_talwar]

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