topic Re: total restricted values in QlikView

total restricted values

Tue, 25 Feb 2014 18:55:16 GMT

hi,

i have a straight table, and i use "dimension limits" to display only the top X items in the data using to the first expression in the table (sum(sales)). X is determined by the user.

what i'm trying to do now is to add a "Totals" row for the table, but i need it to sum only the top X items, as opposed to all the items in the data. any ideas?

thanks

Re: total restricted values

Nicole-Smith — Tue, 25 Feb 2014 18:58:23 GMT

Instead of using dimension limits, you can use set analysis in your expression. So an example (if your X was 2):

sum({<Customer={'=rank(sum(Sales))<=2'}>}Sales)

Re: total restricted values

MK_QSL — Tue, 25 Feb 2014 19:10:23 GMT

Like this?

Re: total restricted values

aveeeeeee7en — Tue, 25 Feb 2014 19:12:00 GMT

Hi Danny

Use below expression:

For Top 5 Sales:

=if(aggr(rank(sum(Sales)),Sales)<=5,Sales)

Regards

Aviral Nag

Re: total restricted values

Tue, 25 Feb 2014 19:22:46 GMT

hi, i must be doing something wrong. in the table i have [SKU] as a single dimension. i need to display the largest 10 values, using sum([Net Sales] as the expression for the ranking. then, i need to add a total row to sum only these 10 values. i tried to use your advice but for some reason it doesn't work. please see below the expression i used:

sum({<=[SKU]={'=rank(sum([Net Sales]))<=10'}>}[Net Sales])

Re: total restricted values

Nicole-Smith — Tue, 25 Feb 2014 19:23:47 GMT

You have an extra = in your set analysis. It should look like this:

sum({<[SKU]={'=rank(sum([Net Sales]))<=10'}>}[Net Sales])