topic Re: Dimension Limits - Top 80% in QlikView

Dimension Limits - Top 80%

Mon, 15 Jun 2015 15:45:01 GMT

Can someone tell me why when I use the Dimension Limit 80% of Relative Total Using Largest Value, the lowest value is included?

Below are all my values sorted Descending (this is the first expression and I only have one Dimension)

and this is what I get when I set the dimension limit to accumulate to 80% of Relative Total Using Largest Value

shouldn't the record with a total of $5000 come next?

Re: Dimension Limits - Top 80%

Gysbert_Wassenaar — Mon, 15 Jun 2015 16:35:26 GMT

Can you post a Qlikview document that demonstrates the problem?

Re: Dimension Limits - Top 80%

Colin-Albert — Mon, 15 Jun 2015 16:41:01 GMT

What is the sort order on your chart set to?

I would try sorting by Y-Value - descending

Re: Dimension Limits - Top 80%

sunny_talwar — Mon, 15 Jun 2015 16:41:56 GMT

Try selecting the "Include Boundary Values" and see if that helps.

Best,

Sunny

Re: Dimension Limits - Top 80%

Mon, 15 Jun 2015 18:48:39 GMT

Sorry I can't post the document, confidential info.

Re: Dimension Limits - Top 80%

Mon, 15 Jun 2015 18:52:02 GMT

When I select include Boundary Values, the record totaling $5000 is added to the results but the -110 still shows up.

Re: Dimension Limits - Top 80%

Mon, 15 Jun 2015 18:55:10 GMT

You can't sort by y-values in a straight table (x & y don't exist). I tried sorting my dimension with first priority descending by the same expression formula and that didn't change anything.

Re: Dimension Limits - Top 80%

Mon, 15 Jun 2015 19:09:07 GMT

I added a % of total column and this is confusing me even more. You reach 80% at the 10,265 line, so why are the other 3 included? Is it sorting individual records and taking the top 80% before aggregation and then including records that aren't in the 80% in the aggregated result?

Every day I work in QV I wish I was working in Tableau again.

Re: Dimension Limits - Top 80%

sunny_talwar — Mon, 15 Jun 2015 19:26:02 GMT

May be you want to restrict negative values in your expression, because negative value potentially reduces the total to be under 80%

{<Invoice = {'>0'}>}

Re: Dimension Limits - Top 80%

antoniotiman — Mon, 15 Jun 2015 20:07:11 GMT

Hi Jessica,

I have already noticed this behaviour due to values < 0.

You don't use Dimension limits.

You use expression

If(RangeSum(Above(Sum(Sales),0,RowNo()))/Sum(TOTAL Sales) <= 0.8,Sum(Sales))

Dimension Product

Regards,

Antonio

Re: Dimension Limits - Top 80%

MarcoWedel — Mon, 15 Jun 2015 20:37:57 GMT

Hi,

seems this question has already been discussed some years ago:

QV 11 Dimension Limits with negative values

https://community.qlik.com/message/173938#173938

so in essence it:

"Works as designed."

hope this helps

regards

Marco

Re: Dimension Limits - Top 80%

Gysbert_Wassenaar — Tue, 16 Jun 2015 07:13:25 GMT

Well, I didn't ask for that specific qlikview document. Just 'a' qlikview document that demonstrates the problem. If you're worried about confidential information perhaps this document helps: Preparing examples for Upload - Reduction and Data Scrambling

Re: Dimension Limits - Top 80%

Gysbert_Wassenaar — Tue, 16 Jun 2015 07:38:35 GMT

I'm pretty sure that the negative invoice amounts are leading to unexpected behaviour. See attached example. With negative amounts the relative total can go over 100% and you can get two 80% boundaries. I'm not sure there is a correct behaviour in this case. It's like showing negative values in a pie chart. It just doesn't make sense. Negative sized areas are not possible (outside exotic physics).

Re: Dimension Limits - Top 80%

rajeshvaswani77 — Tue, 16 Jun 2015 07:44:28 GMT

Select Global Grouping Mode.

regards,

Rajesh Vaswani

Re: Dimension Limits - Top 80%

antoniotiman — Tue, 16 Jun 2015 08:03:06 GMT

Hi Jessica,

see Attachment.

Regards,

Antonio