Top 100 based on a tertiary parameter

[#1]

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!

[sunny_talwar]

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

[#3]

Hi Sunny,

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

[sunny_talwar]

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

[#5]

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

[sunny_talwar]

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.

[#7]

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

[sunny_talwar]

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