topic Re: Pareto Chart in QlikView

Pareto Chart

sreelathamohan — Thu, 06 Feb 2020 17:26:03 GMT

I have a pareto chart in my application (see attached). Some of the dimension values are Null. When I suppress NUll values I don't see the 100% on the Cumulative Line chart. But I show Null values then I can see the cumulative % values until 100%. How do I show 20 - 100% all values and not count Null dimension values for the chart?

Re: Pareto Chart

sunny_talwar — Thu, 06 Feb 2020 18:12:17 GMT

What is your expression for Cumulative %? and what is your dimension name in the chart?

Re: Pareto Chart

sreelathamohan — Thu, 06 Feb 2020 18:14:25 GMT

Cumulative % Expression:

RangeSum(Above(Sum([Quantity Affected]), 0, RowNo())) / Sum(total [Quantity Affected])

Failure Detail is the dimension name

Re: Pareto Chart

sunny_talwar — Thu, 06 Feb 2020 18:18:04 GMT

Try this

RangeSum(Above(Sum([Quantity Affected]), 0, RowNo())) / Sum(TOTAL {<[Failure Detail] *= {"*"}>}[Quantity Affected])

Re: Pareto Chart

sreelathamohan — Thu, 06 Feb 2020 18:29:32 GMT

Not working. That shoots up the total % to more than 100 and I still see the Null dimension values

Re: Pareto Chart

sunny_talwar — Thu, 06 Feb 2020 18:34:55 GMT

Suppress the null dimension now...sorry, I should have told that....

Re: Pareto Chart

sreelathamohan — Thu, 06 Feb 2020 19:56:30 GMT

That worked Sunny. Thank you very much !! I might have another question for you soon.

Re: Pareto Chart

sunny_talwar — Thu, 06 Feb 2020 20:13:21 GMT

Sure thing.

Re: Pareto Chart

sreelathamohan — Thu, 06 Feb 2020 20:14:06 GMT

Sunny - One small issue - the total cumulative % against the last bar shows 100.85%. Why would that be?

Re: Pareto Chart

sreelathamohan — Thu, 06 Feb 2020 20:21:27 GMT

Also some of the bars don't show any value when in fact there are values. Please take a look at the screenshot

Re: Pareto Chart

sreelathamohan — Thu, 06 Feb 2020 20:24:39 GMT

Actually that was a problem with my other expression. So now I see correct values for the bars.

But I still don't see a 100% on the cumulative line against the last bar. It is a 100.85%.

Re: Pareto Chart

sunny_talwar — Thu, 06 Feb 2020 20:25:30 GMT

The questions you are asking are difficult to answer without looking at a sample.. would you be able to share a sample where I can see this?

Re: Pareto Chart

sunny_talwar — Thu, 06 Feb 2020 20:26:14 GMT

Not entirely sure, but try this may be

RangeSum(Above(Sum({<[Failure Detail] *= {"*"}>} [Quantity Affected]), 0, RowNo())) / Sum(TOTAL {<[Failure Detail] *= {"*"}>}[Quantity Affected])

Re: Pareto Chart

sreelathamohan — Thu, 06 Feb 2020 20:35:55 GMT

That did not work.

Re: Pareto Chart

sunny_talwar — Thu, 06 Feb 2020 20:40:09 GMT

I can check if you are able to attach a sample

Re: Pareto Chart

sreelathamohan — Thu, 06 Feb 2020 21:30:58 GMT

I don't think I will be able to attach the entire application. If I send you a sample excel with the dimension and measure values will you be able to tell?

Re: Pareto Chart

sreelathamohan — Thu, 06 Feb 2020 21:49:52 GMT

I think something is wrong with the cumulative % expression. Please take a look at the excel file. Columns A, B, C are from the chart and D and E manually calculated.

If you look at the first row you will see that there is a discrepancy between values in values in col C and col E.

Re: Pareto Chart

sreelathamohan — Thu, 06 Feb 2020 21:50:51 GMT

Here is the file.

Re: Pareto Chart

sreelathamohan — Thu, 06 Feb 2020 22:11:43 GMT

I am actually trying to accomplish what's on the attached file. Show cumulative % until 80% and the rest as "Others" and a spike from 80% to 100%. Any help is appreciated.

Re: Pareto Chart

sunny_talwar — Fri, 07 Feb 2020 14:02:16 GMT

What is this giving you as an expression in a text object? Does it give you 133 or not?

Sum(TOTAL {<[Failure Detail] *= {"*"}>}[Quantity Affected])