topic Re: Variables inside Set Analysis in QlikView

Variables inside Set Analysis

bkn — Thu, 02 Apr 2015 16:29:07 GMT

I have a variable which store expression. I need to use that variable in Set Analysis.

Variable = vData

Eg. =SUM({<Account={"Prime"}>}vData) // this does not work

Re: Variables inside Set Analysis

MK_QSL — Thu, 02 Apr 2015 16:35:17 GMT

Try '$(vData)'

Re: Variables inside Set Analysis

Anonymous — Thu, 02 Apr 2015 16:40:18 GMT

It depends what the expression is inside the variable. If it contains an aggregation (e.g. sum or count), you have sum( of sum()), which is not allowed. You have to use aggr:

sum(aggr(sum(),fields))

In your case probably something like this:

SUM({<Account={"Prime"}>} aggr(vData,<list of fields here>) )

Re: Variables inside Set Analysis

bkn — Thu, 02 Apr 2015 16:45:17 GMT

DOES NOT WORK

Re: Variables inside Set Analysis

bkn — Thu, 02 Apr 2015 16:45:48 GMT

What fields will be here <list of fields here>

Re: Variables inside Set Analysis

Anonymous — Thu, 02 Apr 2015 16:49:27 GMT

This is something I cannot guess - it is specific to your application and the chart. If you can upload your app or a simplified example, then I (or someone else) will be able to help.

Re: Variables inside Set Analysis

vvvvvvizard — Thu, 02 Apr 2015 17:32:40 GMT

try just =$(vdata)

Re: Variables inside Set Analysis

bkn — Thu, 02 Apr 2015 19:02:16 GMT

=NUM((SUM({<[ID]={"=Rank(Aggr(SUM([Current Quarter]),[ID]))<=20"}>} $(vData))),'$#,##0')

Sorry, doesnot work!!!

Re: Variables inside Set Analysis

ramoncova06 — Thu, 02 Apr 2015 19:08:40 GMT

what is inside of you variable ?

once you provide that we can understand what can be causing the problem

Re: Variables inside Set Analysis

bkn — Thu, 02 Apr 2015 19:55:16 GMT

These expressions are inside variable:

If(UnbilledMonthName='FEB', SUM({<UnbilledMonthName={'NOV'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='MAR', SUM({<UnbilledMonthName={'DEC'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='APR', SUM({<UnbilledMonthName={'JAN'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='MAY', SUM({<UnbilledMonthName={'FEB'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='JUN', SUM({<UnbilledMonthName={'MAR'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='JUL', SUM({<UnbilledMonthName={'APR'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='AUG', SUM({<UnbilledMonthName={'MAY'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='SEP', SUM({<UnbilledMonthName={'JUN'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='OCT', SUM({<UnbilledMonthName={'JUL'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='NOV', SUM({<UnbilledMonthName={'AUG'},UnbilledFYYear={'$(vFYPriorYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='DEC', SUM({<UnbilledMonthName={'SEP'},UnbilledFYYear={'$(vFYPriorYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='JAN', SUM({<UnbilledMonthName={'OCT'},UnbilledFYYear={'$(vFYPriorYear)'}>}[Unbilled Current Quarter])))))))))))))

Re: Variables inside Set Analysis

ramoncova06 — Thu, 02 Apr 2015 20:28:52 GMT

ok then try with

If(UnbilledMonthName='FEB', aggr(SUM({<UnbilledMonthName={'NOV'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='MAR', aggr(SUM({<UnbilledMonthName={'DEC'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='APR', aggr(SUM({<UnbilledMonthName={'JAN'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='MAY', aggr(SUM({<UnbilledMonthName={'FEB'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='JUN', aggr(SUM({<UnbilledMonthName={'MAR'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='JUL', aggr(SUM({<UnbilledMonthName={'APR'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='AUG', aggr(SUM({<UnbilledMonthName={'MAY'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='SEP', aggr(SUM({<UnbilledMonthName={'JUN'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='OCT', aggr(SUM({<UnbilledMonthName={'JUL'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='NOV', aggr(SUM({<UnbilledMonthName={'AUG'},UnbilledFYYear={'$(vFYPriorYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='DEC', aggr(SUM({<UnbilledMonthName={'SEP'},UnbilledFYYear={'$(vFYPriorYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='JAN', aggr(SUM({<UnbilledMonthName={'OCT'},UnbilledFYYear={'$(vFYPriorYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)))))))))))))

Re: Variables inside Set Analysis

Peter_Cammaert — Thu, 02 Apr 2015 21:28:38 GMT

Your set expression produces an empty set, unless ID can contain the string

"=Rank(Aggr(SUM([Current Quarter]),[ID]))<=20"

This is a string and not an expression that you want to be evaluated and produce 0 (false) or -1. (true).

First test without set expression and with the big IF instead of a variable. If that works, replace the big IF by the variable substitution $(vData). If that works, add a correct set expression. That way, you'll gradually build up a very complex anf functioning expression without loosing track of where exactly things start to go wrong.

Best,

Peter