Dimension limits with negative values sorted in ascending order in bar chart

[#1]

Hi all!

I have an issue with the tab "Dimension Limits". I want to show only the smallest 10 values in a bar chart. To do so, I mark the checkbox but than I get the error message "No data to display". When I chance to show only the largest 10 values it works correctly. I want all values to be sorted in ascending order by the y-value and I need to be sure that only negative value will be shown. The expression I use is something like this:

IF

(

(

CEIL(MAX(KeyFigure1)) +

CEIL(AVG(KeyFigure2)) +

CEIL(AVG(KeyFigure3)) -

CEIL(AVG(KeyFigure4))

) < 0,

CEIL(MAX(KeyFigure1)) +

CEIL(AVG(KeyFigure2)) +

CEIL(AVG(KeyFigure3)) -

CEIL(AVG(KeyFigure4))

)

When I use the formula below it works, but in this case I can not be sure to take negative values into account only:

CEIL(MAX(KeyFigure1)) +

CEIL(AVG(KeyFigure2)) +

CEIL(AVG(KeyFigure3)) -

CEIL(AVG(KeyFigure4))

Thanks in advance for any hint or help!

FYI - I use QlikView version 11.0.11426.0 SR2 (x64).

Cheers,

Martin.

[CELAMBARASAN]

May be there is a possibility of problems in null values.

Check with this

IF

(

RangeSum(

CEIL(MAX(KeyFigure1)),

CEIL(AVG(KeyFigure2)),

CEIL(AVG(KeyFigure3)),

CEIL(AVG(KeyFigure4))*-1

) < 0,

RangeSum(

CEIL(MAX(KeyFigure1)),

CEIL(AVG(KeyFigure2)),

CEIL(AVG(KeyFigure3)),

CEIL(AVG(KeyFigure4))*-1

)

)

[#3]

Hey!

Thanks for your fast feedback! Unfortunately this does not work. I mean if I want to show the largest 10 values it works but not for the smallest 10.... Can this be a bug? However the data I compute with my formula are just a list of values. If I have 30 values computed and 20 of these 30 values are negative I just want to show the Top 10 negative values.....strage behavior.

Do you have some more ideas?

Thanks!!!

[dasilva]

Hi,

I have the same problem in a bar chart after upgrade to QV11 SR2; could definetely be a bug. Have you reported it to QT?

[#5]

Hi,

Thanks for your feedback! No, i don't have reported it to QT! However, I found a workaround, as works as follows: 1st step --> Use a RANK function in order to compute the 10 smallest vaules, 2nd step --> Compute your values 3rd step --> Enable the x-axis scrollbar on the presentation tab

IF

(
NUM(RANK(
IF(
   (
     CEIL(MAX(KeyFigure1))     +

     CEIL(AVG(KeyFigure2))     +
     CEIL(AVG(KeyFigure3))     -
     CEIL(AVG(KeyFigure4))     ) < 0,
RANGESUM(
     CEIL(MAX(KeyFigure1)),
     CEIL(AVG(KeyFigure2)),
     CEIL(AVG(KeyFigure3)),
     CEIL(AVG(KeyFigure4))*(-1)
     )*(-1)) ,4)) <= 10, 

     CEIL(MAX(KeyFigure1))     +
     CEIL(AVG(KeyFigure2))     +
     CEIL(AVG(KeyFigure3))     -
     CEIL(AVG(KeyFigure4))
     )

Cheers,

Martin.

[sarahallen1]

I have just discovered this same problem.  I want to use "smallest" or "first" in the dimension limits, but then it says "no data to display".  I have raised it with QV support.

[miikkaqlick]

Hi!

You must rewrite your expression. I had same problem with expression like this:

Sum( {set} if( x<y, amount))

/

Sum( {set} if( x<y, amount2))

I changed expression to

Sum( {set} if( x<y, amount/amount2))

and now it works.

Br,

Miikka

[sarahallen1]

Miikka I think your before/after expressions could give different results, as division is not an additive function

[miikkaqlick]

Hi!

You are right. I only made that as a experiment if that bug is expression dependent. I had 4 charts in app and in 2 limiting dimension worked before and in 2 it didn't. I wondered what's the mechanism for that and it was the expression. I have to solve that in script so that I can write simple expression like Sum( {set} amount).

Br,

Miikka

Climber Finland

[sarahallen1]

Thanks Miikka.

NB if it helps anyone, when I raised it with support, they confirmed that the expression needed to be rewritten in a different way.