topic Re: Comparison using Set Analysis in QlikView

Comparison using Set Analysis

Tue, 20 Dec 2016 18:12:13 GMT

Hi All,

Hope you are doing good.

I have to display the first year where sale is less than the sale in the first row.

Country	YEAR	Sales
INDIA	2015	500
AUSTRALIA	2016	876
CANADA	2017	999
USA	2014	250
AFRICA	2016	300

Here USA is the first whose sale is less than sale in the First row (i.e 250 < 500).

Hence my solution should be 2014.

Please help.

Thanks a lot in advance.

Re: Comparison using Set Analysis

Gysbert_Wassenaar — Wed, 21 Dec 2016 10:10:53 GMT

Perhaps like this: Min({<Sales={"<$(=Only({<YEAR={$(=FieldValue('YEAR',1))}>}Sales))"}>}Year)

Re: Comparison using Set Analysis

swuehl — Wed, 21 Dec 2016 10:13:51 GMT

How is 'first row' defined in your application, i.e. is there any sort order of that table?

Re: Comparison using Set Analysis

Thu, 22 Dec 2016 21:32:31 GMT

Hi Gysbert,

Thanks a lot for the response.

This seems to be very helpful.

Re: Comparison using Set Analysis

Thu, 22 Dec 2016 21:33:52 GMT

Hi Stefan,

No I don't have any sort order in my table.

Thanks

Re: Comparison using Set Analysis

swuehl — Thu, 22 Dec 2016 22:07:06 GMT

Just to note that above uses LOAD order as sort order.

And you might run into troubles if you have YEAR values appear more than once (because of the use of Only() ), like shown for year 2016 in your sample data.

Re: Comparison using Set Analysis

Tue, 27 Dec 2016 21:16:50 GMT

Thanks a lot Stefan for your valuable comments.