topic Re: Set Analysis with Expression Variable in QlikView

Set Analysis with Expression Variable

Wed, 22 Oct 2014 14:20:38 GMT

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)

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

Thanks in advance!

Re: Set Analysis with Expression Variable

Gysbert_Wassenaar — Wed, 22 Oct 2014 14:25:01 GMT

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

Wed, 22 Oct 2014 18:15:17 GMT

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

Gysbert_Wassenaar — Wed, 22 Oct 2014 18:22:48 GMT

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

Wed, 22 Oct 2014 19:18:47 GMT

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

gmoraleswit — Wed, 22 Oct 2014 20:54:00 GMT

You can do a search on a list box

Re: Set Analysis with Expression Variable

Gysbert_Wassenaar — Thu, 23 Oct 2014 16:37:44 GMT

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

Anonymous — Thu, 23 Oct 2014 18:36:50 GMT

It is also missing a closing Double quote:

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

Re: Set Analysis with Expression Variable

Gysbert_Wassenaar — Thu, 23 Oct 2014 18:41:13 GMT

Good point. Well spotted!