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: 
marcus_steggall
Creator
Creator

Top 10 customers, linked to variables in a slider

Good day,

Hope all is well and hoping you can help me with a quick query    

Basically I have the following calculation, that enables me to create a slider using variables, where you can choose the min and max customer amounts to include/exclude.

=Num(Sum({$<Year_YYYY={'$(=vMaxYear)'},Customer={"=Sum(amount)<=$(vMinLimit) or Sum(amount)>=$(vMaxLimit)"}>} amount)/1000,'#,###0')

I now want to create another table, just to show me a top 10, as per calculation below

=Num(sum({<Year_YYYY={'$(vMaxYear)'},Customer = {'=rank(sum(amount),4)<=10'}>} amount)/1000,'#,###0')

I would like to put this top 10 ranking calc, into the above variables calculation, but am struggling to combine the two.

I attach a qvd for refrence

Any help, would be great.

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Try this expression

IF(

rank(total Sum({$<Year_YYYY={'$(=vMaxYear)'},Customer={"=Sum(amount)<=$(vMinLimit) or Sum(amount)>=$(vMaxLimit)"}>}amount)/1000)<=10,

Num(Sum({$<Year_YYYY={'$(=vMaxYear)'},Customer={"=Sum(amount)<=$(vMinLimit) or Sum(amount)>=$(vMaxLimit)"}>}amount)/1000,'#,###0')

)

View solution in original post

6 Replies
MK_QSL
MVP
MVP

Try this expression

IF(

rank(total Sum({$<Year_YYYY={'$(=vMaxYear)'},Customer={"=Sum(amount)<=$(vMinLimit) or Sum(amount)>=$(vMaxLimit)"}>}amount)/1000)<=10,

Num(Sum({$<Year_YYYY={'$(=vMaxYear)'},Customer={"=Sum(amount)<=$(vMinLimit) or Sum(amount)>=$(vMaxLimit)"}>}amount)/1000,'#,###0')

)

marcus_steggall
Creator
Creator
Author

Hi Manish,

Many thanks for taking the time, to giving me some advice

I like the calc, but do you know why this removes the total amount, as you can see below?

Thanks

12-04-2018 12-13-01.bmp

MK_QSL
MVP
MVP

Try

SUM(Aggr(IF(

rank(total Sum({$<Year_YYYY={'$(=vMaxYear)'},Customer={"=Sum(amount)<=$(vMinLimit) or Sum(amount)>=$(vMaxLimit)"}>}amount)/1000)<=10,

Num(Sum({$<Year_YYYY={'$(=vMaxYear)'},Customer={"=Sum(amount)<=$(vMinLimit) or Sum(amount)>=$(vMaxLimit)"}>}amount)/1000,'#,###0')

),Period_Sort,Period_Number,Month))

sunny_talwar

Or if this is a straight table just use Sum of rows on the expressions tab

marcus_steggall
Creator
Creator
Author

ahhh yes, something so simple yet leaves the brain sometimes haha

many thanks Sunny, very helpful as ever

marcus_steggall
Creator
Creator
Author

this was the pefect calc, appreciate the response, its very much appreciated.

Added into the sum expression from Sunny, it's all working

have a good weekend both