Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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.

Thanks in Advance

H.K

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

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

This is what you might be looking for:

Recipe for a Pareto Analysis – Revisited

15 Replies
MVP
MVP

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

This is what you might be looking for:

Recipe for a Pareto Analysis – Revisited

rittermd
Honored Contributor

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.

Not applicable

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

Not applicable

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.

Not applicable

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))

    )

MVP
MVP

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

mkelemen
Contributor III

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

Highlighted
Not applicable

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]))

Issue ScreenShot.PNG

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.

Above Issue.PNG

You can find this in https://help.qlik.com/en-US/sense/3.1/Subsystems/Hub/Content/ChartFunctions/InterRecordFunctions/abo...

Example 3

Thanks!!

HK

Not applicable

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