topic Re: Dimension limits with negative values sorted in ascending order in bar chart in QlikView

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

Fri, 07 Dec 2012 20:05:19 GMT

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:

(

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.

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

CELAMBARASAN — Fri, 07 Dec 2012 20:19:42 GMT

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

Check with this

(

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

)

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

Fri, 07 Dec 2012 21:29:16 GMT

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

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

dasilva — Thu, 13 Dec 2012 12:47:26 GMT

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?

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

Thu, 13 Dec 2012 13:30:04 GMT

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

(
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.

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

sarahallen1 — Thu, 08 Aug 2013 13:50:51 GMT

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.

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

miikkaqlick — Fri, 12 Dec 2014 16:21:08 GMT

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

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

sarahallen1 — Fri, 12 Dec 2014 16:37:50 GMT

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

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

miikkaqlick — Fri, 12 Dec 2014 16:43:30 GMT

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

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

sarahallen1 — Fri, 12 Dec 2014 16:46:32 GMT

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.