8 Replies Latest reply: Oct 23, 2014 2:41 PM by Gysbert Wassenaar

# Set Analysis with Expression Variable

Hi All,

I would like to select values to display in a chart based on an expression variable. For example:

Count({\$<eAvailability={">=\$(=40)"}>} DISTINCT ResourceId)

Where eAvailability = sum of hours available over the next 2 weeks

eAvailability | Sum({\$<WeeksAgo={'>=\$(=-2)<=\$(=0)'}>} AvailableHours)

Based on what I'm reading, I don't think this is possible, but is there another way to get the same result?

Count( {\$ <ResourceId= { "=Sum({\$<WeeksAgo={'>=\$(=-2)<=\$(=0)'}>} AvailableHours)>=40 " } DISTINCT ResourceId)

OR

Count({\$ <ResourceId= P({1<Sum({\$<WeeksAgo={'>=\$(=-2)<=\$(=0)'}>}AvailableHours)= {">=\$(=40)"}>}ResourceId)>} DISTINCT ResourceId)

Thanks in advance!

• ###### Re: Set Analysis with Expression Variable

You're correct that you can't use the expression variable on the left side in place of a field name. Depending on your data this could work: Count( {\$ <ResourceId= { "=Sum({\$<WeeksAgo={'>=-2<=0'}>} AvailableHours)>=40 " } DISTINCT ResourceId).

• ###### Re: Set Analysis with Expression Variable

Thanks Gysbert! For some reason that function is bringing back no data.

Should the between function be: '>=-2<=0' instead of with the parentheses?

For my own sake:

{\$ <ResourceId= { "=Sum({\$<WeeksAgo={'>=-2)<=0)'}>} AvailableHours)>=40 " }

- This would bring back any ResourceId that has hours above 40 over the next two weeks

Count( {set} DISTINCT ResourceId)

- This would count the distinct resources returned by the set

Am I right?

Is there an easy way to check the ResourceIds that are returned from the first statement? I've tried using the expression in a text box to get the list, but can't get the syntax right.

• ###### Re: Set Analysis with Expression Variable
Should the between function be: '>=-2<=0' instead of with the parentheses?

Ah, yes of course. My mistake.

For my own sake:

{\$ <ResourceId= { "=Sum({\$<WeeksAgo={'>=-2)<=0)'}>} AvailableHours)>=40 " }

- This would bring back any ResourceId that has hours above 40 over the next two weeks

Yes, those resources that have a sum of hours equal or greater than forty in this and the next two weeks.

Count( {set} DISTINCT ResourceId)

- This would count the distinct resources returned by the set

Am I right?

Correct.

• ###### Re: Set Analysis with Expression Variable

This has got to be so close to being correct, but it's coming back with an error in the set modifier expression.

Any ideas on how to view the ResourceIds being returned by this expression:

ResourceId= { "=Sum({\$<WeeksAgo={'>=-2<=0'}>} AvailableHours)>=40

• ###### Re: Set Analysis with Expression Variable

You can do a search on a list box

• ###### Re: Set Analysis with Expression Variable
ResourceId= { "=Sum({\$<WeeksAgo={'>=-2<=0'}>} AvailableHours)>=40

The above is missing a closing curly brace:

ResourceId= { "=Sum({\$<WeeksAgo={'>=-2<=0'}>} AvailableHours)>=40 }

If that doesn't solve the problem then it would be helpful to look at the document you're working on. Can you upload a small document that demonstrates the issue? See this document if you're worried about confidential information: Preparing examples for Upload - Reduction and Data Scrambling

• ###### Re: Set Analysis with Expression Variable

It is also missing a closing Double quote:

ResourceId= { "=Sum({\$<WeeksAgo={'>=-2<=0'}>} AvailableHours)>=40 "}

• ###### Re: Set Analysis with Expression Variable

Good point. Well spotted!