Issues with Aggr function with dates and targets

Thu, 05 Sep 2013 14:13:01 GMT

Hi Guys,

I have 2 expressions called A and B, both of which are trying to count the number of days an agent worked and multiplies it by a target value unique to them. Neither of which are doing it correctly.

I'm using the following formulas to try and calculate this;

Expression A:

Sum(Aggr(Count(DISTINCT CallDate & Agent) * [Total Target],Agent))

and

Expression B:

COUNT({$<[Cam/Acc] = {'Account'}>}Distinct CallDate & Agent) *

(Sum({$<[Cam/Acc] = {'Account'}>}[Total Target]) / COUNT({$<[Cam/Acc] = {'Account'}>}DISTINCT Agent))

Expression A gives me the correct total, however, when viewed in a table against Dimension 'CallDate', it seems to show the outcome of the expression against random Calldates.

Expression B gives me the correct values by dimension but the total is incorrect and changes when the dimesion is cyclic (CallDate - Week - Month).

I've attach the file so you can see what i mean.

Thank you in advance.

Regards,

Nick

Re: Issues with Aggr function with dates and targets

Gysbert_Wassenaar — Thu, 05 Sep 2013 16:56:17 GMT

sum(aggr(sum([Total Target]),CallDate)) maybe?

Re: Issues with Aggr function with dates and targets

Fri, 06 Sep 2013 08:06:38 GMT

Thanks G Wassenaar!,

Spot on!

topic Issues with Aggr function with dates and targets in QlikView

Issues with Aggr function with dates and targets

Re: Issues with Aggr function with dates and targets

Re: Issues with Aggr function with dates and targets