Comparison using Set Analysis

[#1]

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.

[Gysbert_Wassenaar · Accepted solution]

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

[swuehl]

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

[#4]

Hi Gysbert,

Thanks a lot for the response.

This seems to be very helpful.

[#5]

Hi  Stefan,

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

Thanks

[swuehl]

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.

[#7]

Thanks a lot Stefan for your valuable comments.