How to use set analysis with a variable

[#1]

I have a variable defined as vAHT = sum(TotalTime)/Sum(TotalCalls).

I need to show this variable over a 12 months period (past 12) and I have a set expression like this:

sum({<CalPeriod={">$(=Max(CalPeriod)-12) <=$(=Max(CalPeriod))"},Year=,Month=,Day=>}TotalTime)/

sum({<CalPeriod={">$(=Max(CalPeriod)-12) <=$(=Max(CalPeriod))"},Year=,Month=,Day=>}TotalCalls)

I want to change it use the variable only - something like

sum({<CalPeriod={">$(=Max(CalPeriod)-12) <=$(=Max(CalPeriod))"},Year=,Month=,Day=>}$(vAHT))

However I am not getting the correct answer - can anyone help or advise what I am doing wrong?

Thanks,
Amit

[Anonymous]

I think that it is not possible to use nested aggregation besides using aggr().

--> basically after vAHT was replaced by "sum(TotalTime)/Sum(TotalCalls)"

your formula looks like that:

sum( .... sum(TotalTime)/Sum(TotalCalls))

[marcus_sommer · Accepted solution]

Like Robin mentioned you creates a nested aggregation which is without the use of aggr() not valid. Your variable would be need to extend to:

vAHT = aggr(sum(TotalTime)/Sum(TotalCalls), Dim1, Dim2)

whereby that meant that you would use an aggr-function where it won't really needed and should be therefore better avoided then it adds unnecessary complexity and decreased the performance (especially in your case you would need to determine which conditions belong the inner- and which to the outer-aggregation).

More practically to simplify the expressions might be to put the condition within an variable, like:

vAHT = {<CalPeriod={">$(=Max(CalPeriod)-12) <=$(=Max(CalPeriod))"},Year=,Month=,Day=>}

and then as expression:

sum($(vAHT) TotalTime) / sum($(vAHT) TotalCalls)

or the use of parametrized variables, like:

vAHT = {<CalPeriod={">$(=Max($1)-$2) <=$(=Max($1))"},Year=,Month=,Day=>}

and then as expression:

sum($(vAHT(CalPeriod, 12)) TotalTime) / sum($(vAHT(CalPeriod, 12)) TotalCalls)

More to the use of variables could you find here: Variables

- Marcus

[effinty2112]

Hi Amit,

               In your expression:

sum({<CalPeriod={">$(=Max(CalPeriod)-12) <=$(=Max(CalPeriod))"},Year=,Month=,Day=>}$(vAHT))

you're summing an average which doesn't really make sense. It's best to work out an average, which will be the ratio of two quantities, at the final stage of a calculation.

Kind regards

Andrew

[Anonymous]

HI Amit,

Use your variable as function:

Your variable: sum($1 TotalTime)/Sum( $1 TotalCalls)

Your expression

$(YourVar({<CalPeriod={">$(=Max(CalPeriod)-12) <=$(=Max(CalPeriod))"},Year=,Month=,Day=>}))

Regards!!!

[#6]

Thank you all for your feedback.