7 Replies Latest reply: Mar 16, 2016 9:36 AM by David Schwarz

# Missing bars in bar chart

Hi

I want to create a bar chart in Qlik sense with a predifined bin size.

To achive that I created (see sample File attached) a Dimension: Class([y],0.5) and a measure Count([y]).

My Problem is that the bins "33.5<=x<33" and "33<=33.5" are missing since no Value of y lies in These two bins.

Does a simple (or complicated ) method exist to add These bins (with a Count value of 0)?

Thanks for reading and hopfully you have more ideas :-)

Cheers,

David

• ###### Re: Missing bars in bar chart

Hi David, this may be a question for hic or jpe - if they know I am sure they will reply. I am interested as well.

After you get a proper reply with (hopefully) a solution, please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

• ###### Re: Missing bars in bar chart

Hi David,

One way to do this is to create a new table and populate it with all possible ranges for bins.Then you can link that table to your fact table.

--------------------------------------------------------------------

// Add new field that will hold class values

[Sheet1]:

x,

y,

Class(y,0.5) as [bins for y *]

FROM [lib://Desktop/Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

//Get the max value of [y]

TempY:

Max(y) as MaxY

Resident Sheet1;

Let vMaxY = Peek('MaxY',0,'TempY');

//Create a temp table with values for interval start, from 0 to max y

IntervalsTemp:

RowNo()/2-0.5 as IntervalStart

autogenerate(Ceil(\$(vMaxY),1)*2);

//Create a table with all possible intervals that will link to fact table

Intervals:

Class(IntervalStart,0.5) as [bins for y *]

Resident IntervalsTemp;

// Clean up !

Drop Table TempY;

Drop Table IntervalsTemp;

--------------------------------------------------------------------

Of course, there might be a more elegant solution than this.

Alex

• ###### Re: Missing bars in bar chart

Hi Alex

Your solution goes in the right direction.
Where is only a small Problem left: The x-axis goes until 1857.5-1858 and shows a count of 1.
Do you see a solution for that?

• ###### Re: Missing bars in bar chart

David,

I see the same in your original chart. What would be your expected result?

Alex

• ###### Re: Missing bars in bar chart

Hi Alex

Of course you are right :-)
Was too tired...

I have an extension of my question, will create a new post for it.

Thanks a lot.
David

• ###### Re: Missing bars in bar chart

feel free to tag me in the new post, would be glad to help further. I still have your app in my QS desktop and even used it as a teaching tool today

• ###### Re: Missing bars in bar chart

Hi Alex

Happy to do that :-)

Cheers,
David