topic Re: Set Analysis in QlikView

Set Analysis

marco_puccetti — Tue, 25 Aug 2015 10:13:48 GMT

Hello i need to know if is anything wrong in this expression, it doesn't return me the expected values:

Count({<[FIELD A]={"=$([FIELD A])-1"}}DISTINCT [FIELD B])

Thanks

Marco

Re: Set Analysis

sunny_talwar — Tue, 25 Aug 2015 10:15:28 GMT

Try this may be:

Count({<[FIELD A]={"=$([FIELD A])-1"}>}DISTINCT [FIELD B])

Re: Set Analysis

sunny_talwar — Tue, 25 Aug 2015 10:16:01 GMT

Count({<[FIELD A]={"$(=[FIELD A])-1"}>}DISTINCT [FIELD B])

Re: Set Analysis

marco_puccetti — Tue, 25 Aug 2015 10:38:28 GMT

Hello with this statement:

=Count({<[FIELD A]={"=$([FIELD A])-1"}>}DISTINCT [FIELD B])

return exactly the same data returned with this one:

Count(distinct [FIELD B])

applied on a selection on FIELD A.

Is it possible?

Thanks

Marco

Re: Set Analysis

Tue, 25 Aug 2015 10:41:53 GMT

Is FIELDA only a number list from where you are selecting only one value? If yes, try this
=Count({<[FIELD A]={"=$([FIELD A]-1)"}>}DISTINCT [FIELD B])

Re: Set Analysis

sunny_talwar — Tue, 25 Aug 2015 10:41:59 GMT

What filters are you trying to put through your set analysis ({<[FIELD A]={"=$([FIELD A])-1"}>})?

Re: Set Analysis

marco_puccetti — Tue, 25 Aug 2015 12:55:57 GMT

Field A is a list of year and the table rows get the data from that list.

I have tried with the below expression but it doesn't print the previous year data.

Count({<={"=$(-1)"}>}DISTINCT )

Thanks

Marco

Re: Set Analysis

marco_puccetti — Tue, 25 Aug 2015 13:46:28 GMT

I have tried to check the correct form of the "$(-1)" ststement being an year but the computated dimension is not working with this stetement: =$(-1).

Anyone can explain me how to get this data inside the table?

Thanks

Marco

Re: Set Analysis

sasiparupudi1 — Tue, 25 Aug 2015 13:56:47 GMT

try

Count({<[FIELD A]={"=[FIELD A]-1"}}DISTINCT [FIELD B])

Re: Set Analysis

marco_puccetti — Tue, 25 Aug 2015 14:31:26 GMT

It doesn't work.

I have tried also with this but it's still not working

Count({<={"=$(=(-1))"}>}DISTINCT )

Thanks

Marco

Re: Set Analysis

maxgro — Tue, 25 Aug 2015 15:11:27 GMT

I think this only works when you select 1 value in FIELD A

=Count({<[FIELD A]={"$(=([FIELD A])-1)"}>}DISTINCT [FIELD B])

maybe

=Count({<[FIELD A]={"$(=max([FIELD A])-1)"}>}DISTINCT [FIELD B])

but it's still not clear what do you want to achieve

Re: Set Analysis

Peter_Cammaert — Tue, 25 Aug 2015 15:18:00 GMT

The reason that this doesn't work is that set analysis is evaluated outside of a chart to reduce/expand the data rows that go into the chart. This evaluation happens before any calculation is performed.

The logical consequence, as Massimo already pointed out, is that -1 will return all currently selected values in field and then try to subtract 1 from that result. Which only works if you select a single value in .

Set analysis does not work row-by-row, but globally for all data used in a chart.

Peter

Re: Set Analysis

Peter_Cammaert — Tue, 25 Aug 2015 15:20:39 GMT

Oh and by the way: for a single value you better use something like ...{'$(=-1)'}

Re: Set Analysis

marco_puccetti — Tue, 25 Aug 2015 15:28:57 GMT

I have also tried with this

Count(DISTINCT {<={"=$(-1)"}>})

but doesn't work.

Thanks

Marco

Re: Set Analysis

Digvijay_Singh — Tue, 25 Aug 2015 15:54:16 GMT

But this is different than what pcammaert has suggested, try single quote as suggested. But restrict selection of single value only in list box.

Re: Set Analysis

marco_puccetti — Wed, 26 Aug 2015 08:11:19 GMT

I have tried but it is still not working.

Any other ideas?

Thanks

Marco

Re: Set Analysis

marco_puccetti — Wed, 26 Aug 2015 09:02:07 GMT

I have tried with this statement using the above but it still not working:

=Count(DISTINCT {< = {"=$(above())"}>})

Where A is a dimension of the table and B is an expression.

The above works fine but if used within the expression doesn't work.

Thanks

Marco

Re: Set Analysis

marco_puccetti — Mon, 31 Aug 2015 13:41:30 GMT

Hello, anybody knows what's wrong about this formula?

=Count({< [year = {"=$(=Num(above([year]),'0'))"}>}distinct [object])

I need to count the sum of object of a previous year.

For me this is correct infact the Num(above([year]),'0') works, but not if inserted in the Count expression.

Thanks

Marco

Re: Set Analysis

maxgro — Mon, 31 Aug 2015 14:16:29 GMT

I think there are some syntax error, this should be ok from a syntax point of view

=count( {$ <year={"$(=above(year))"}>} distinct object)

but I'm not sure above and set analysis together make sense (above is an inter record function, set analysis is calculated once for chart)

what do you want to achieve?

count distinct object when the year is ....???

Re: Set Analysis

marco_puccetti — Mon, 31 Aug 2015 14:36:03 GMT

I need to get the sum comparison between one year row and the above, that could not be sequentially.

Used singularly the above works perfectly, but not in the expression.

Is it possible to write a formula in order to get a value like this?

Thanks

Marco