Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mrthomasshelby
Creator III
Creator III

Pareto Count with 2 Dimensions?

Hi all,

I have a pivot table as shown below:

Sc1.PNG

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.

Attaching the qvf for your ref. Thanks in advance! @sunny_talwar @tresesco 

1 Solution

Accepted Solutions
sunny_talwar

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:
LOAD *,
	 [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;

image.png

View solution in original post

3 Replies
sunny_talwar

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:
LOAD *,
	 [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;

image.png

mrthomasshelby
Creator III
Creator III
Author

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

sunny_talwar

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.