Top value of 100 using set analysis?

[#1]

Hi

I have a database like below

Date               Sales

2016-01-01        65

2016-02-01        75  

2016-03-01         81

2016-04-01          92

2016-05-01          99

2016-06-01        102

2016-07-01         111

2016-08-01        154

2016-09-01        201

2016-10-01         2650

2016-11-01         568

2016-12-01         5689

i want get a date which one is sales=100 or near to 100  i.e  102  (2016-06-01 )

How to get this

Thiru

[sunny_talwar · Accepted solution]

May be like this:

FirstSortedValue({<Sales = {'>=100'}>}Date, Sales)

[Anonymous]

see the below script and attached QVW file. Hope this helps you

Nearto100_filter:

Load

min(Sales) as Sales_filter

;

Load

Date,

Sales

where Sales>=100

;

LOAD * INLINE [

Date,               Sales

2016-01-01,        65

2016-02-01 ,       75 

2016-03-01 ,        81

2016-04-01 ,         92

2016-05-01 ,         99

2016-06-01 ,       102

2016-07-01  ,       111

2016-08-01   ,     154

2016-09-01   ,     201

2016-10-01   ,      2650

2016-11-01    ,     568

2016-12-01     ,    5689

]

;

Nearto100_Final:

Load

Date,

Sales

where Exists(Sales_filter,Sales)

;

LOAD * INLINE [

Date,               Sales

2016-01-01,        65

2016-02-01 ,       75 

2016-03-01 ,        81

2016-04-01 ,         92

2016-05-01 ,         99

2016-06-01 ,       102

2016-07-01  ,       111

2016-08-01   ,     154

2016-09-01   ,     201

2016-10-01   ,      2650

2016-11-01    ,     568

2016-12-01     ,    5689

]

;

[Anonymous]

Yaar stalwar1 i should learn some of the Set Analysis from you. you have improved a lot. Happy to see that.

[sunny_talwar]

Thanks vinay_bangari , this was for nothing if I had not got good training from you.

[effinty2112]

Hi Thiru,

This will give the date which has Sum(Sales) closest in absolute value to 100. If there is a tie for first place it will return those dates separated by a comma.

=Concat({$<Date ={"=Aggr(Rank(-fabs(Sum(Sales)-100),0,1),Date)=1"}>}Date,',' )

Cheers

Andrew

[#7]

Hi

FirstSortedValue({<Sales = {'>=100'}>}Date, Sales)

Is the correct Answer

Thanks Sunnny

Thiru

[#8]

Thanks Sunny

[sunny_talwar]

No problem, please close this thread by marking correct and helpful responses if you got what you were looking for.

Best,

Sunny

[Anonymous]

Thiru i see a correct answer from Sunny. So please close the thread by marking correct and helpful answers.