topic Re: Set Analysis issue in QlikView

Set Analysis issue

alec1982 — Fri, 20 Jun 2014 04:22:41 GMT

Hi,

I have the following formula in a straight table. It works when one value of the dimension is selected while it doesn't when nothing is selected and it only shows null.

MaxString({$< [Option Date]={"$(=Min({<[Option Type] ={'*'}-{'EXP'}>}[Option Date]))"}>}[Option Type])

I tried to take off the second set analysis and still doesn't work..

MaxString({$< [Option Date]={"$(=Min([Option Date])"}>}[Option Type])

Any idea what could be wrong and why it works only when I select a value in the dimension

Hope someone can help.

Thxs,

Alec

Re: Set Analysis issue

tresB — Fri, 20 Jun 2014 04:29:04 GMT

Try:

MaxString({$< [Option Date]={"$(=Min({<[Option Type] - ={'EXP'}>}[Option Date]))"}>}[Option Type])

Re: Set Analysis issue

alec1982 — Fri, 20 Jun 2014 04:33:41 GMT

Hi,

Thank you for the reply but it doesn't work. same it shows null both if there is something selected or not..

Re: Set Analysis issue

tresB — Fri, 20 Jun 2014 04:35:46 GMT

Could you post a sample?

Re: Re: Set Analysis issue

alec1982 — Fri, 20 Jun 2014 04:50:35 GMT

Hi ,

I extracted the file into a small qvw and loaded only the columns used for this expression. it gave me two rows and they are wrong.

I am expecting to see one line per ID with the minimum date related to i and the option related on that date..

Please see attached..

Thxs

Re: Re: Set Analysis issue

alec1982 — Fri, 20 Jun 2014 05:03:03 GMT

Hi ,

I guess on the date column we can use the following expression

MinString({$<[Option Type]={'*'}-{'EXP'}>}[Option Date])

But i am not able to show the type associated with the dates

Re: Set Analysis issue

tresB — Fri, 20 Jun 2014 05:50:53 GMT

Probaly this simpler way.

Dimension: %Key

Exp1: Min([Option Date]

Exp2: FirstSortedValue( [Option Type], [Option Date])

Re: Re: Set Analysis issue

alec1982 — Fri, 20 Jun 2014 05:55:55 GMT

Hi,

Thank you for your help but this won't work.

1. if you notice on my script I am trying to exclude one of the types 'EXP'

2. The sample I sent is not the full set of the data within this straight table. There are more columns and first stored value might not work..

3. Looking at the file that you sent and filtering by one of the keys.. the value returned on the option is not to related date.

Re: Set Analysis issue

tresB — Fri, 20 Jun 2014 06:00:31 GMT

Exp2: =FirstSortedValue({<[Option Type]-={'EXP'}>}[Option Type],[Option Date])

Re: Set Analysis issue

alec1982 — Fri, 20 Jun 2014 13:48:21 GMT

Thxs!