Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
)
)
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!!!
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?
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.
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.
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
Miikka I think your before/after expressions could give different results, as division is not an additive function
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
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.