variable in set analysis

[#1]

Hi everyone

I haven't managed to find an example that answers my question, so hopefully you are able to help.

I have a variable which results in either a 1 or a 0.  If this is possible, what would be the correct syntax as I always get an error.

sum( {$< $(myVariable) = {1}>}  field)

Thanks

Oli

[Gysbert_Wassenaar]

Unless your variable returns a field that cannot work. Only fields can be used on the left side of the comparison for set modifiers. sum( if( $(myVariable) = 1, field) ) will work. But it doesn't make much sense if myVariable doesn't vary.

[#3]

Hi

Thanks for the quick response. I think what I was trying to do will not work.

I have a pivot table filtered to show one product.  Each product will have a nett price a selection of special customer prices and various promotional prices.  I want to narrow this down for ease of viewing to just 6 prices.  The lowest Promo price, the Top 5 cheapest customer specials and the nett price.

I have created a variable that identifies which rows I want to keep (marked with a -1) and the rest are blank ( -).  What I am trying to figure out is how to automatically filter these from the pivot table

myVariable

(if(Type='Nett',-1)) or (if((rank(if(Type = 'Range' ,-Price),5)<=5)=-1,-1)) or (if((rank(if(Type = 'Promo' ,-Price),1)=1)=-1,-1))

I can't seem figure out which road to take to achieve my goal.

PS I would post my workbook but it sensitive data.  Maybe, i'll be able to mock something up as an example...

Thanks again

[#4]

You could scramble your data - Then it would be easier. If that is possible

[whiteline]

Hi.

I have created a variable that identifies which rows I want to keep (marked with a -1) and the rest are blank ( -).  What I am trying to figure out is how to automatically filter these from the pivot table

Are you talking about variable or field ?

[#6]

Hi

I have managed to create a sample file which hopefully better demonstrates my question

[whiteline]

Ok.

To use set analysis this way you should add some field for selection. I simply added rowno.

[Sample Data]:

LOAD 
      rowno() as RowID,
      *
Inline

...

Then use set analysis search feature and dollar sign expansion properly to make a selection in RowID field.

For example, for Nett expression: Only({<RowID={"=$(vSelectedLowPrices)=-1"}>}Price)

[whiteline · Accepted solution]

Another way is to use calculated dimension. Just replace Comment dimension with

=aggr(if($(vSelectedLowPrices)=-1, Comment), Comment)

Then toggle 'Suppress when value is null' chekbox to hide rows with dashes.

Usually it's slower than set analysis, but useful when you have a lot of expressions, or want to use simple expressions.

[#9]

Thankyou whiteline - you're a god send.

I have recently picked up QlikView due to work and am finding it tricky (Excel is my forte).

Anyway, just to clarify your answers, I have added them to the attached file.  I wanted to make sure there was a demo to help others and make certain I have followed you correctly.

The first works well but the rows remain visble but empty.

The second works a treat.  I had tried this method but had only suceeded in getting calcualtion errors

From what I can tell aggr seem to appear alot on these forums and is a very powerful function?

Thank you for your time.

Oli

[whiteline]

It seems that the first one doesn't work as supposed because there is an expression for BackGroundColor 😃

Yes, aggr is very usefull. You can start by reading QlikView build-in help.