Issue in implementing Pareto Analysis.

[amber_jain]

Dear All,

I am facing an issue related to implementation of one of the logic in Qlikview. In the attached screenshot you can see there are some defects displayed in the combo chart with their count, and their is a 'Others' that is count of other defects.

Other Defects = Total Defects - Count of top 10 defects.

125= 169 - 44.

I want to create an expression that should only display me 44 defects (that is top 10 defects)

Also I want an expression that displays individual values like 17 , 6 , 4 , 3 , 3 etc.

This is because I need to implement Pareto Analysis.

Below is the expression I have used to calculate no of defects. (Kindly ignore the database fields)

=

count(if($(vCondition_DateRange) and (Stato = 2 and IdPtoRisoluzione<>IdPtoDelibera and Anomalia_Month = month(Date(floor([Dte_Risoluzione])))) , CodAnomalia))
+
count(if($(vCondition_DateRange) and ( Stato = 2 and Anomalia_Month <> month(Date(floor([Dte_Risoluzione]))) ) , CodAnomalia))
+
count(if($(vCondition_DateRange) and ( Stato = 1) , CodAnomalia))

+

count( distinct if($(vCondition_DateRange) and
(Date(floor([Dte_Insert])) <> Date(floor([Dte_Risoluzione])) and Date(floor([Dte_Risoluzione]))<> date(vardate,'DD') and month(Date(floor([Dte_Insert])))=month(Date(floor([Dte_Risoluzione])))and Date(floor([Dte_Risoluzione]))>date(vardate,'DD')) and IdPtoRisoluzione=IdPtoDelibera , Dte_Insert))

[sunny_talwar]

Are you showing top 10 using the dimension limits tab? If you are, then may be uncheck 'Others' to not show the others part? For individual values, you might be able to use Max(Aggr(YourExpressionHere, [Defect Description]))

[amber_jain]

Dear Sunny,

Thanks for your response.

I knew I selected 'Others' option in Dimensional Limits tab. I just used for reference purpose.

I used your expression , max(aggr($(vOutDefects), Defect_Description)) , but it is not giving output as per requirement.

I should get a line having following values:

1st  defect (11) : 11/34 

2nd defect (3) : (11+3) / 34

3rd defect (3) : (11+3+3)/34

4th defect (3) : (11+3+3+3)/34

5th defect (3) : (11+3+3+3+3)/34

6th defect (2) : (11+3+3+3+3+2)/34

................ till 10th defect where 34 is sum of top 10 defects. 

Can you please help me in this? PFA the screenshot

[sunny_talwar]

Are you looking to modify this line?

It might be easy to work with a sample qvw where we can test and try out the expression.

[amber_jain]

Yes Sunny 🙂 you are correct. This is a combo chart , bars are appearing fine, need to bring the logic in line.

Let me know if you require QVW. Actually this is a small part of my large dashboard development of QV, thats why not shared qvw file.

Regards,

Amber Jain.

[sunny_talwar]

It would be easier to help if you are able to share a qvw here.

[johnca]

Using the values you presented in your image I created the attached QVW (I added a few extra values to create the Others bucket). Your formulas for both defect counts should be the same. For the line use full accumulation. And as suggested on Dimension Limits use Largest 10 Values and unselect Show Others.

Of course, replace my simple formula with yours.

HTH,

John

[amber_jain]

Hi John,

Thank you for your response. I got the same using Full Accumulation. 

But I need the data points in the line like below:

1st data point : 11/34

2nd data point: (11+3)/34

3rd data point: (11+3+3)/34

4th data point: (11+3+3+3)/34

.... and 10th data point : (11+3+3+3+3+3+2+2+2+2)/34

where 34 is the sum of top 10 defects that is correct.

My current expression is giving sum of all the defects for the selection.

Thanks and Regards,

Amber Jain

[sunny_talwar · Accepted solution]

using the sample provided by @johnca I have used this expression without Full Accumulation

Aggr(RangeSum(Above(Sum({<defect = {"=Rank(Sum(qty)) < 11"}>} qty), 0, RowNo())), (defect, (=Sum(qty), desc)))/Sum(TOTAL {<defect = {"=Rank(Sum(qty)) < 11"}>} qty)

Which gives me

 

[johnca]

Yeah, Sunny, I always like the expression approaches as they are better controlled but I was thinking "only show 10". I also should have made mine example show percentages rather than raw accumulation values.

But, using rank can show more than the top 10 and . What if there was a 20-way tie for Rank <= 10? Or 50-way tie? Is this a desired result in a combo chart? In my experience, especially when the Rank is displayed alongside the data, I only visibly show 10 whatsits and the user has to see that.

Your example is is more dynamic though.

Regards,

John