topic Re: Filter by Measure in App Development

Filter by Measure

john_delligatti — Tue, 20 Mar 2018 13:51:11 GMT

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,

Can I create buttons (list boxes without an extension) based on a measure?
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!

Re: Filter by Measure

sunny_talwar — Tue, 20 Mar 2018 17:11:05 GMT

Look here

Recipe for a Pareto Analysis – Revisited

Re: Filter by Measure

john_delligatti — Thu, 22 Mar 2018 13:59:22 GMT

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

Re: Filter by Measure

sunny_talwar — Thu, 22 Mar 2018 14:13:07 GMT

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

)

Re: Filter by Measure

john_delligatti — Thu, 22 Mar 2018 14:36:56 GMT

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?

Re: Filter by Measure

sunny_talwar — Thu, 22 Mar 2018 14:43:20 GMT

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

)

Re: Filter by Measure

john_delligatti — Thu, 22 Mar 2018 15:12:23 GMT

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

Re: Filter by Measure

sunny_talwar — Thu, 22 Mar 2018 15:13:06 GMT

Awesome, I am glad it finally worked