textbox rank sum set analysis

[Anonymous]

hi there,

ı have a table like that ;

term	sales
201711	90
201710	90
201709	99
201708	90
201707	89
201706	88
201705	86
201704	85
201703	84
201702	96
201701	81
201612	80
201611	79
201610	79
201609	77
201608	77
201607	75
201606	76
201605	73
201604	74
201603	72
201602	71
201601	70
201512	70
201511	69
201510	68
201509	66
201508	66

I want to write in the textbox  that ;

'in the last (12) months of term, rank=1 and rank=2 sum(sales) are .99., 96...  and in these months are .201709 and 201702. '

again I am having difficulty with the syntaxes of set analysis......

[aarkay29]

try this

FirstSortedValue({$<term={">=$(=Date(addmonths(Max(term),-11)))"}>}sales,-sales,1)

FirstSortedValue({$<term={">=$(=Date(addmonths(Max(term),-11)))"}>}sales,-sales,2)

[antoniotiman]

May be this

[vishsaggi]

May be other ways like below:

= '1st: '  & Max(Aggr(Sum(sales), term),1) & Chr(13) &

   '2nd: ' & Max(Aggr(Sum(sales), term),2) & Chr(13) & 'OR' & Chr(13) &

   '1st: '  & FirstSortedValue(sales, -Aggr(Sum(sales), term),1) & Chr(13) &

   '2nd: ' & FirstSortedValue(sales, -Aggr(Sum(sales), term),2)

[YoussefBelloum]

try this:

'in the last (12) months of term, rank=1 and rank=2 sum(sales) are'

&Sum({<term = {"=Rank(Sum({<term>}sales)) = 1"}>}sales)

&' and '

& Sum({<term = {"=Rank(Sum({<term>}sales)) = 2"}>}sales)

[antoniotiman]

Text Box

=Concat({<term={"=Rank(Sum(sales),0,1) <= 2"}>} term&' '&'Sales = '&sales,Chr(10),-sales)

[vishsaggi]

And to get the dates try

   '1st:' & FirstSortedValue(term & ':' & sales, -Aggr(Sum(sales), term),1) & Chr(13) &

   '2nd:' & FirstSortedValue(term & ':' & sales, -Aggr(Sum(sales), term),2)

[Anonymous]

not work