topic Re: Set Analysis with variable in QlikView

Set Analysis with variable

pauldamen — Wed, 17 Jul 2013 10:16:01 GMT

All,

I have 120 projects, I want to count the number of projects which are within budget. The sum is calculated as follows: Sum(Budget) - sum(Actuals)

I thought to make a set analysis in which a created a variable for the calculation of the difference:

Count({< '$(vProjectResult)' = {'>0'}>} [Project ID])

But whatever I try I can't get the variable to work in that place (I tried all kinds of quotes, brackets etc)

My question is, is this possible to create and if so how?

And if not what would be the best way for me to do this?

Regards,

Paul

Re: Set Analysis with variable

jonathandienst — Wed, 17 Jul 2013 10:36:29 GMT

Did you try

Count({<$(=vProjectResult) = {'>0'}>} [Project ID])

Jonathan

Re: Set Analysis with variable

pauldamen — Wed, 17 Jul 2013 10:56:09 GMT

Hi,

Thanks for the reply Jonathan.

I did try that as well, but no luck.

Any other options?

Regards,

Paul

Re: Set Analysis with variable

kaushiknsolanki — Wed, 17 Jul 2013 10:59:31 GMT

Hi Paul,

If its possible for you to create a new field in script which will be Budget - Actuals

Then it will be easier.

All you need is

Count({<Amount= {'>0'}>} [Project ID])

Regards,

Kaushik Solanki

Re: Set Analysis with variable

pauldamen — Wed, 17 Jul 2013 11:01:54 GMT

Good idea!

My budget and Actuals come from 2 different sources though. How can I create one field in the script?

Re: Set Analysis with variable

kaushiknsolanki — Wed, 17 Jul 2013 11:07:10 GMT

Hi,

Create a another table which will have project wise Budget, Actual and the difference.

Then link that with your project Master.

Regards,

Kaushik Solanki

Re: Set Analysis with variable

pauldamen — Wed, 17 Jul 2013 11:14:36 GMT

I created all the tables and added the expression to calculate the difference, but it says it is an invalid expression:

Sum([2013 Budget])+Sum([2014 Budget])+Sum([2012 Budget])-Sum(EACRem) as ProjectResult

Re: Set Analysis with variable

Wed, 17 Jul 2013 11:15:27 GMT

try this

Count(if(vProjectResult >0, [Project ID]))

or try this

Count({<$(=vProjectResult) = {">0"}>} [Project ID])