Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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