topic Re: variable in set analysis in QlikView

variable in set analysis

Tue, 14 May 2013 14:38:14 GMT

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

Re: variable in set analysis

Gysbert_Wassenaar — Tue, 14 May 2013 14:46:00 GMT

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.

Re: variable in set analysis

Tue, 14 May 2013 15:58:35 GMT

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

Re: variable in set analysis

Tue, 14 May 2013 19:41:12 GMT

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

Re: variable in set analysis

whiteline — Wed, 15 May 2013 06:49:53 GMT

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 ?

Re: variable in set analysis

Wed, 15 May 2013 08:19:17 GMT

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

Re: variable in set analysis

whiteline — Wed, 15 May 2013 11:03:44 GMT

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)

Re: variable in set analysis

whiteline — Wed, 15 May 2013 11:22:21 GMT

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.

Re: variable in set analysis

Wed, 15 May 2013 11:38:42 GMT

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

Re: variable in set analysis

whiteline — Wed, 15 May 2013 17:04:44 GMT

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.