Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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!

8 Replies
MVP & Luminary
MVP & Luminary

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


talk is cheap, supply exceeds demand
Not applicable

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.

Highlighted
MVP & Luminary
MVP & Luminary

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.


talk is cheap, supply exceeds demand
Not applicable

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

Partner
Partner

Re: Set Analysis with Expression Variable

You can do a search on a list box

Capture.JPG.jpg

MVP & Luminary
MVP & Luminary

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


talk is cheap, supply exceeds demand
stevenrtaylor
Contributor II

Re: Set Analysis with Expression Variable

It is also missing a closing Double quote:

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

MVP & Luminary
MVP & Luminary

Re: Set Analysis with Expression Variable

Good point. Well spotted!


talk is cheap, supply exceeds demand