topic Re: Top 100 based on a tertiary parameter in App Development

Top 100 based on a tertiary parameter

Thu, 23 Feb 2017 06:26:40 GMT

Hi All,

I have a dimension called Quarter and I have another dimension called Compliance (which has only 2 values - Compliant/Non-Compliant). Other important fields are Supplier Name, Value (which basically details the spend for each line item). I want to create a stacked bar chart showing the percentage of spend on Compliant and Non-Compliant Suppliers for each quarter. This is quite straightforward and after declaring Quarter and Compliance as dimensions, I used the following expression.

sum({$}Value)/sum({$}TOTAL <Quarter> Value)

This worked great. However, now I want to create the same stacked % bar graph for Compliant and Non-Compliant suppliers - BUT want to consider the spend of only the Top 100 Suppliers (based on spend).

Would appreciate your help on this. Thanks in advance!

Re: Top 100 based on a tertiary parameter

sunny_talwar — Thu, 23 Feb 2017 10:27:37 GMT

Dimensions are still Quarter and Compliance? You want Top 100 Suppliers for each Quarter or Top 100 Suppliers across the total period?

Re: Top 100 based on a tertiary parameter

Tue, 28 Feb 2017 02:13:02 GMT

Hi Sunny,

Thanks for your help. I want the top 100 suppliers for each quarter.

Re: Top 100 based on a tertiary parameter

sunny_talwar — Tue, 28 Feb 2017 03:57:34 GMT

What is your dimension? Would you be able to share a sample?

Re: Top 100 based on a tertiary parameter

Tue, 28 Feb 2017 06:46:14 GMT

Dimension is Quarter. Unfortunately, I can't share a sample due to data sensitivity.

Re: Top 100 based on a tertiary parameter

sunny_talwar — Tue, 28 Feb 2017 10:08:44 GMT

I am not asking for the real data. I am just looking for mocked up data just to understand the kind of data you have.

Re: Top 100 based on a tertiary parameter

Fri, 10 Mar 2017 05:14:00 GMT

Hi Sunny,

Sorry for the late response.

Please find attached a representation of the sample data. In this representation, for example, i would want to take a look at the top 3 or 4 suppliers.

Regards,

Prashant

Re: Top 100 based on a tertiary parameter

sunny_talwar — Fri, 10 Mar 2017 14:01:37 GMT

What is the expected output for the sample provided? Can you check if this is what you want?

Top 3

Sum(Aggr(If(Rank(Sum(Value)) < 4, Sum(Value)), Quarter, Compliance, [Supplier Name]))/Sum(TOTAL <Quarter> Aggr(If(Rank(Sum(Value)) < 4, Sum(Value)), Quarter, Compliance, [Supplier Name]))

Top 4

Sum(Aggr(If(Rank(Sum(Value)) < 5, Sum(Value)), Quarter, Compliance, [Supplier Name]))/Sum(TOTAL <Quarter> Aggr(If(Rank(Sum(Value)) < 5, Sum(Value)), Quarter, Compliance, [Supplier Name]))