Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need help with a summation problem

Hi all,

I would like to have a object with two columns the first showing amount and the second showing hours.

In the first row I want to show our largest client and in the second row I would like to show the total sum of our 5 largest clients. My problem is that I want both columns to be sorted on the amount. Both columns should show the sum of the 5 largest clients ranked on the amount.

In the attached example the column with the hours shows the wrong value because it takes the five largest clients sorted in hours instead of value. for the five largest clients it summarizes to 280 hours but I want it to add up to 240 hours. how do I do if I want it to be sort of the five largest clients in terms of value insted of of hours

Many thanks in advance

Magnus

1 Solution

Accepted Solutions
MVP
MVP

Hi Magnus,

try this for your expression:

=IF(Largestclientrow=10,

max(aggr(sum(hours),Client,Largestclient),1)

,

IF(Largestclientrow=20,

sum(aggr(if(rank(sum(Amount),4)<=5,sum(hours)),Largestclient,Client))

,

IF(Largestclientrow=40,

sum(hours)

)))

You could reuse the same expression to calculate your other TopX values, too, just replace the <=5 with =1 or <=40

Regards,

Stefan

P.S. just saw Lasses solution, which probably works fine. Only disadvantage with FirstSortedValue is, that it is returning NULL if more than one value of expression share the same lowest sort-order.

edit

View solution in original post

3 Replies
Partner
Partner

Hi,

you can use FirstSortedValue:

IF(Largestclientrow=10,

          FirstSortedValue(hours,-Amount,1)

,

IF(Largestclientrow=20,

          FirstSortedValue(hours,-Amount,1)

          +FirstSortedValue(hours,-Amount,2)

          +FirstSortedValue(hours,-Amount,3)

          +FirstSortedValue(hours,-Amount,4)

          +FirstSortedValue(hours,-Amount,5)

,

IF(Largestclientrow=40,

sum(hours)

)))

BR

Lasse

MVP
MVP

Hi Magnus,

try this for your expression:

=IF(Largestclientrow=10,

max(aggr(sum(hours),Client,Largestclient),1)

,

IF(Largestclientrow=20,

sum(aggr(if(rank(sum(Amount),4)<=5,sum(hours)),Largestclient,Client))

,

IF(Largestclientrow=40,

sum(hours)

)))

You could reuse the same expression to calculate your other TopX values, too, just replace the <=5 with =1 or <=40

Regards,

Stefan

P.S. just saw Lasses solution, which probably works fine. Only disadvantage with FirstSortedValue is, that it is returning NULL if more than one value of expression share the same lowest sort-order.

edit

View solution in original post

Not applicable

Thank you Lasse an Swuehl

It is working perfect!