topic Re: How to use set analysis with a variable in QlikView

How to use set analysis with a variable

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to use set analysis with a variable

Anonymous — Thu, 08 Sep 2016 18:54:22 GMT

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))

Re: How to use set analysis with a variable

marcus_sommer — Fri, 09 Sep 2016 08:26:48 GMT

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

Re: How to use set analysis with a variable

effinty2112 — Fri, 09 Sep 2016 09:17:25 GMT

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

Re: How to use set analysis with a variable

Anonymous — Fri, 09 Sep 2016 09:42:54 GMT

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!!!

Re: How to use set analysis with a variable

Fri, 09 Sep 2016 13:18:52 GMT

Thank you all for your feedback.