Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

pauldamen
Contributor II

Set Analysis with variable

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

7 Replies
MVP
MVP

Re: Set Analysis with variable

Hi

Did you try

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

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
pauldamen
Contributor II

Re: Set Analysis with variable

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

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

pauldamen
Contributor II

Re: Set Analysis with variable

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

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

pauldamen
Contributor II

Re: Set Analysis with variable

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

Not applicable

Re: Set Analysis with variable

try this

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

or try this

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

Community Browser