Filter by Measure

[john_delligatti]

Hi All,

I am looking to make selections based on a measure in Qliksense. I have created the measure below to tell me whether our company spend is “managed” or “tail” spend based on the accumulated spend %. (We had 3 classifications based on 0-8%0, 80%-90% and 90%+ originally which is why you see the 3 options).

if(RangeSum(Above(Sum([Total Spend]),1,RowNo()))/Sum(Total [Total Spend])<=.8,'Managed', if(RangeSum(Above(Sum([Total Spend]),1,RowNo()))/Sum(Total [Total Spend])<=.9,'Tail','Tail'))

From this, I need to create a button (or listbox to keep it simple) where we are able to select what is “managed” and what is “tail"

2 questions about this,

1. Can I create buttons (list boxes without an extension) based on a measure?
2. Given the fact that this equation responds to other selections, will this create an issue? For example, when I filter down by client or category, a supplier may go from being in the “tail” category to the “managed” category. We want to preserve that functionality so commodity managers can see views specific to their client and category. I believe if we ARE able to filter by the measure, selecting “tail” would cause the equation to recalculate and the top 80% of those “tail” suppliers will be labeled as “managed”

Thank you!

[sunny_talwar]

Look here

Recipe for a Pareto Analysis – Revisited

[john_delligatti]

Thank you Sunny! I was able to get the pareto class to appear as a dimension, however I am stuck on one part.  From the linked article the author describes the following:

If you really want it to be dynamic, it should cause no problems. However, it will mean that the classification will change the moment you select one of the Pareto groups. So a second click on a Pareto class will reduce the data sample further. This will be confusing: "Now, how many times did I click on class A?"

An alternative that I think is better is if you use {$<Product=>} as set expression (assuming that the Pareto classification is made on the field Product). Then you will have a dynamic classification that is based on all fields except the Product.

HIC

I attempted to use this in my equation below, and this causes it to error out. There are no filters selected. Is there something incorrect with my Syntax?

=Aggr(

    If(Rangesum(Above(Sum({1} [Total Spend])/Sum({1} total [Total Spend]),1,RowNo()))<0.8, 'Managed',

        If(Rangesum(Above(Sum({1} [Total Spend])/Sum({1} total [Total Spend]),1,RowNo()))<0.9, 'Tail',

            'Tail')),

    ({$<[Supplier Name]=>} ,(=Sum({1} [Total Spend]),Desc))

    )

Thanks

[sunny_talwar]

May be this is what you wanted?

=Aggr(

    If(Rangesum(Above(Sum({1} [Total Spend])/Sum({1} total [Total Spend]),1,RowNo()))<0.8, 'Managed',

        If(Rangesum(Above(Sum({1} [Total Spend])/Sum({1} total [Total Spend]),1,RowNo()))<0.9, 'Tail',

            'Tail')),

     ([Supplier Name], (=Sum({$<[Supplier Name]=>} [Total Spend]),Desc))

    )

or this

=Aggr(

    If(Rangesum(Above(Sum({1} [Total Spend])/Sum({1} total [Total Spend]),1,RowNo()))<0.8, 'Managed',

        If(Rangesum(Above(Sum({1} [Total Spend])/Sum({1} total [Total Spend]),1,RowNo()))<0.9, 'Tail',

            'Tail')),

     ([Supplier Name], (=Sum({1} [Total Spend]),Desc))

    )

[john_delligatti]

Hi Sunny,

Both equations work but not to do exactly what I'm looking for. I'm creating a Pareto chart to show which suppliers make up the 80/20 of our spend data. I was hoping to be able to filter my numerous fields ie: Client name, category name, etc.

Both equations will only show whether a supplier is "managed" or "tail" based on % of total company spend, applying filters does not reduce the total spend so therefore the "managed" or "tail" categorization does not update, due to the {1} in the set analysis.

What I am hoping for is a dynamic function that will update Pareto classifications based on certain filters displayed in a table, but also the functionality to drop an equation into a dimension and created a "managed" or "tail" list box that will update the table, WITHOUT recalculating those values once a selection is made in the list box.

Is that possible?

[sunny_talwar · Accepted solution]

Try this in that case

=Aggr(

    If(Rangesum(Above(Sum({<[Supplier Name]>} [Total Spend])/Sum({<[Supplier Name]>} total [Total Spend]),1,RowNo()))<0.8, 'Managed',

        If(Rangesum(Above(Sum({<[Supplier Name]>} [Total Spend])/Sum({<[Supplier Name]>} total [Total Spend]),1,RowNo()))<0.9, 'Tail',

            'Tail')),

    ([Supplier Name], (=Sum({<[Supplier Name]>} [Total Spend]),Desc))

    )

[john_delligatti]

Works perfectly! Your "MVP" status is well deserved!! Thank you!

[sunny_talwar]

Awesome, I am glad it finally worked