Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Not applicable

Top value of 100 using set analysis?

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

1 Solution

Accepted Solutions

Re: Top value of 100 using set analysis?

May be like this:

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

View solution in original post

18 Replies

Re: Top value of 100 using set analysis?

May be like this:

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

View solution in original post

vinay_bangari
Valued Contributor III

Re: Top value of 100 using set analysis?

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

]

;

vinay_bangari
Valued Contributor III

Re: Top value of 100 using set analysis?

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

Re: Top value of 100 using set analysis?

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

effinty2112
Honored Contributor

Re: Top value of 100 using set analysis?

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

Not applicable

Re: Top value of 100 using set analysis?

Hi



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


Is the correct Answer


Thanks Sunnny



Thiru

Not applicable

Re: Top value of 100 using set analysis?

Thanks Sunny

Re: Top value of 100 using set analysis?

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

Best,

Sunny

vinay_bangari
Valued Contributor III

Re: Top value of 100 using set analysis?

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