Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

textbox rank sum set analysis

hi there,

ı have a table like that ;

  

termsales
20171190
20171090
20170999
20170890
20170789
20170688
20170586
20170485
20170384
20170296
20170181
20161280
20161179
20161079
20160977
20160877
20160775
20160676
20160573
20160474
20160372
20160271
20160170
20151270
20151169
20151068
20150966
20150866

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......

7 Replies
aarkay29
Specialist
Specialist

try this

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

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

antoniotiman
Master III
Master III

May be this

vishsaggi
Champion III
Champion III

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
Champion
Champion

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
Master III
Master III

Text Box

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

vishsaggi
Champion III
Champion III

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 applicable
Author

not work