Issues building a histogram dinamicaly - Calculated Dimensions

[fernandotoledo]

Hi everybody!

I´m trying to create a Histogram in qlikView.

To do that I use a simple class() function in a calculated dimension, and a simple agregation such count(distinct ProductID) as the expression.

The problem is that if there is any class with value = 0 or null() it won´t apear in the charts! Even checking "Show all values" and unchecking "supress zeroes / missing values".

When displaying the data in a bar chart, with X axis set as continuous, I get almost what I want, but it messes the legend and I want to see the data as a straight table too.

I tried out many things such set analisys. A sample QVW is atached!

Any sugestions are welcome,

Best regards

Fernando

[#2 · Accepted solution]

The reason it is not showing up is that there are no records that match that element of the calculated dimension (as you know). I had a similar issue come up in the past and I was unable to resolve it while using a calculated dimension. Since there is nothing that matches that element of the dimension, it will never show. It gets even worse when you start making selections.

Eventually, I created an inline table to store the values I needed for the dimension. Something like:

LOAD * INLINE [
 _Low, _High, _Label
 26000, 26999, 26000 <= x < 27000
 27000, 27999, 27000 <= x < 28000
 28000, 28999, 28000 <= x < 28000
 ...

You can then build the expressions using a complicated nested if statement or it can also be done using an Aggr function. Neither are very easy to put together, but it should be able to do what you want.

I was able to modify the expression I used to work with your data. Here is the expression needed. I'll post a sample as well.

Sum(TOTAL <_Label>
 If(Aggr(Sum(VALOR), PRODUTO, _Label) >= _Low and
 Aggr(Sum(VALOR), PRODUTO, _Label) < (_High + 1)
 , VALOR)
)

[fernandotoledo]

Just great! I couldn´t do better!

Thank you very much!

Fernando