Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be like this:
FirstSortedValue({<Sales = {'>=100'}>}Date, Sales)
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
]
;
Yaar stalwar1 i should learn some of the Set Analysis from you. you have improved a lot. Happy to see that.
Thanks vinay_bangari , this was for nothing if I had not got good training from you.
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
Hi
FirstSortedValue({<Sales = {'>=100'}>}Date, Sales)
Is the correct Answer
Thanks Sunnny
Thiru
Thanks Sunny
No problem, please close this thread by marking correct and helpful responses if you got what you were looking for.
Best,
Sunny
Thiru i see a correct answer from Sunny. So please close the thread by marking correct and helpful answers.