Qlik Community

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:

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
Highlighted

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:
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
Highlighted

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

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

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.