Set Analysis with Expression Variable

[#1]

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!

[Gysbert_Wassenaar]

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

[#3]

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.

[Gysbert_Wassenaar]

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.

[#5]

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

[gmoraleswit]

You can do a search on a list box

[Gysbert_Wassenaar]

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

[Anonymous]

It is also missing a closing Double quote:

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

[Gysbert_Wassenaar]

Good point. Well spotted!