15 Replies Latest reply: Jan 31, 2017 3:45 AM by Matus Kelemen

# Display only top N% by Calculated Measure (Qlik Sense)

Hello Qlik gurus,

I have a requirement to show only top % of of the records in a table based on a calculated measure Sum(Units)/ Sum(Total Units). It might be trivial but I did search for it in all the forums with no luck.

H.K

• ###### Re: Display only top N% by Calculated Measure (Qlik Sense)

This is what you might be looking for:

Recipe for a Pareto Analysis – Revisited

• ###### Re: Display only top N% by Calculated Measure (Qlik Sense)

Thanks sunny, will look into it and let you know if it worked for me

• ###### Re: Display only top N% by Calculated Measure (Qlik Sense)

That was a good read Sunny, But I am looking to show only a top N% of records in the table which I am guessing needs to be done using the limitation option if I am not wrong. I can't think of a way on how I can use the below mentioned kind of expression in the Limitations Bar.

=Aggr(

If(Rangesum(Above(Sum({1} Sales)/Sum({1} total Sales),1,RowNo()))<0.8, 'A',

If(Rangesum(Above(Sum({1} Sales)/Sum({1} total Sales),1,RowNo()))<0.9, 'B',

'C')),

(Product,(=Sum({1} Sales),Desc))

)

• ###### Re: Display only top N% by Calculated Measure (Qlik Sense)

Would you be able to share a sample or some sample data? We might be able to offer better help with a sample to look at what exactly you are trying to get

• ###### Re: Display only top N% by Calculated Measure (Qlik Sense)

Hi Sunny

Below is my data set, I’ve stripped my app to simplify it and show the only functionality I am looking for.

As shown in the below screenshot, I have

Product# - Dimension, Straight forward

Dollars – Measure, Sum (Dollars)

Report % Dollars – Measure, Sum(Dollars)/Sum(Total Dollars) in %

Cumulative % Dollars - rangesum(Above(sum([Dollars]), 0, RowNo()))/sum(total([Dollars]))

Here the requirement is to let’s say (display top 18% - This needs to change based on user input) by Cumulative % Dollars, I need to display only the selected list of records in the table. In order for me to do that the only option in Qlik Sense is to use Limitation where I am not sure what expression and which type of Limitation (Exact Value or Relative Value)I need to use. Qlik Sense notes are unclear on this topic about using expression.

Also there seems to be a bit issue in using the Above function as it does not work well with multiple dimensions As shown below.

Example 3

Thanks!!

HK

• ###### Re: Display only top N% by Calculated Measure (Qlik Sense)

Try this.

For your Dimension you can go into the properties and you will see a Limitation box.  You can chose.Fixed Number, Exact value or Relative Value.

If you pick Relative Value then you can chose >, < etc and then what percentage.  It appears that it will do this based on the first measure in your object.  Not sure if you can just change that or if you need to rearrange the order of the measures.

Hope that helps.

• ###### Re: Display only top N% by Calculated Measure (Qlik Sense)

Hi Mark,

Thanks for your reply.As you have mentioned, we can do the relative value for the first measure only. I have multiple measures in my table and unfortunately I cannot change the sequence of measures.

• ###### Re: Display only top N% by Calculated Measure (Qlik Sense)

Hi,

in that case create a first measure that will not be displayed (hide it in Presentation) but will still be used in the Dimension Limits. You can also put a variable expression and change it based on which expression you need to lead the Dimension Limit (see attached file).

BR,

Matus

• ###### Re: Display only top N% by Calculated Measure (Qlik Sense)

Hi Matus,

That was my first Instinct too. But looks like Qlik sense does not have a functionality to hide columns in Tables.

Thanks!!

HK

• ###### Re: Display only top N% by Calculated Measure (Qlik Sense)

You're right, I forgot that it is qlikview only.
What you can do is put the limitation directly into expressions.
Check the example - you can select the dimension and the result is only <=50% of Sum() of the selected dimension. I have it hardcoded but you can use variable. Disadvantage is that it also needs to be sorted by the same dimension because it uses Above().
BR,

Matus

• ###### Re: Display only top N% by Calculated Measure (Qlik Sense)

Matus,

For some reason I am not able to see any attached file in your reply. Can you please double check form your end.

• ###### Re: Display only top N% by Calculated Measure (Qlik Sense)

Let's try again.

• ###### Re: Display only top N% by Calculated Measure (Qlik Sense)

That example is perfect in Qlik View. But in Qlik sense the Limitation option is quite rigid in terms of using 1st measure only.

Thanks!!

HK

• ###### Re: Display only top N% by Calculated Measure (Qlik Sense)

This example is in sense and it does not use Limits.

Matus