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

[#1]

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

[sunny_talwar · Accepted solution]

This is what you might be looking for:

Recipe for a Pareto Analysis – Revisited

[rittermd]

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.

[#4]

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

[#5]

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.

[#6]

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

    )

[sunny_talwar]

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]

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

[#9]

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.

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

Example 3

Thanks!!

HK

[#10]

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