# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
Contributor III

## Pareto Count with 2 Dimensions?

Hi all,

I have a pivot table as shown below:

In this, I'm trying to calculate 80% Potential savings for each Supplier and the number of dimension values(controlled by the panel on the left, 'Levels') that make up this 80% savings. I have written a formula for this as follows:

=Count(Distinct

Aggr(

If(

Rangesum(Above(

Sum(Price_Red)/Sum( total Price_Red)

,1,RowNo()

))<=0.8,\$(vDim1)),

(\$(vDim1),(=Sum(Price_Red),Desc))

)

)

where 'Price_Red' is the savings value and \$(vDim1) is the variable controlling the dimension values that are needed to be counted.

But the thing is, I want to display this count by each Supplier in the table and right now, this only happens if I select each supplier individually from the table.

I tried looking into Sunny's answer here and modified my formula but still doesnt give the required output. Please help me include the Supplier dimension in this calculation.

Labels (7)

• ### QlikView App Development

1 Solution

Accepted Solutions
Highlighted
MVP

## Re: Pareto Count with 2 Dimensions?

So, I had to change few things around in the dashboard.... the new expression that works is this

``````Sum(Aggr(
If(Rangesum(Above(Sum(Price_Red)/Sum(TOTAL <Supplier> Price_Red), 1, RowNo())) < 0.80, 1, 0)

, Supplier, (\$(='[Supplier-' & vDim1 & ']'),(=Sum(Price_Red), Desc))))``````

But in order to do this, I had to create new fields in the script

``````New_Baseline_Final_T8:
[Supplier] & [Specification 1] as [Supplier-Specification 1],
[Supplier] & [Specification 1] as [Supplier-Specification 2],
[Supplier] & [Specification 1] as [Supplier-Specification 3],
[Supplier] & [Specification 1] as [Supplier-Specification 4],
[Supplier] & [Specification 1] as [Supplier-Specification 5],
[Supplier] & [Specification 1] as [Supplier-Specification 6],
[Supplier] & [Specification 1] as [Supplier-Specification 7],
[Supplier] & [No.] as [Supplier-No.],
[Supplier] & [OPU] as [Supplier-OPU],
[Supplier] & [Package Name] as [Supplier-Package Name],
[Supplier] & [Product] as [Supplier-Product]
Resident Baseline_Final_T8;

DROP Table Baseline_Final_T8;
RENAME Table New_Baseline_Final_T8 to Baseline_Final_T8;``````

3 Replies
Highlighted
MVP

## Re: Pareto Count with 2 Dimensions?

So, I had to change few things around in the dashboard.... the new expression that works is this

``````Sum(Aggr(
If(Rangesum(Above(Sum(Price_Red)/Sum(TOTAL <Supplier> Price_Red), 1, RowNo())) < 0.80, 1, 0)

, Supplier, (\$(='[Supplier-' & vDim1 & ']'),(=Sum(Price_Red), Desc))))``````

But in order to do this, I had to create new fields in the script

``````New_Baseline_Final_T8:
[Supplier] & [Specification 1] as [Supplier-Specification 1],
[Supplier] & [Specification 1] as [Supplier-Specification 2],
[Supplier] & [Specification 1] as [Supplier-Specification 3],
[Supplier] & [Specification 1] as [Supplier-Specification 4],
[Supplier] & [Specification 1] as [Supplier-Specification 5],
[Supplier] & [Specification 1] as [Supplier-Specification 6],
[Supplier] & [Specification 1] as [Supplier-Specification 7],
[Supplier] & [No.] as [Supplier-No.],
[Supplier] & [OPU] as [Supplier-OPU],
[Supplier] & [Package Name] as [Supplier-Package Name],
[Supplier] & [Product] as [Supplier-Product]
Resident Baseline_Final_T8;

DROP Table Baseline_Final_T8;
RENAME Table New_Baseline_Final_T8 to Baseline_Final_T8;``````

Highlighted
Contributor III

## Re: Pareto Count with 2 Dimensions?

Thanks Sunny! So the only way to achieve this is to Concat all the possible fields with supplier. Appreciate your help as always!

Highlighted
MVP

## Re: Pareto Count with 2 Dimensions?

In order to get the correct sort order, you will need to concat them unless a particular field won't repeat for multiple supplier. For example, I found that some of the Specification 1 were repeating for multiple Supplier. But if Specification 2 is unique to a supplier, then you don't have to combine it with supplier. But doing it won't do any harm.