Displaying Zero Hires by Month for Different Segments in Line Charts

lawiti

I want to display the total number of hires (on the y-axis) over time, organized by months (on the x-axis), and Segments X,Y,Z (In series)using a line chart. However, there are months when no hires occurred for a specific segment, and in those cases, I want to show zero on the chart instead of having it remain empty. How can I accomplish this?

MichelleH

@lawiti Be sure to remove `Hires` from the Y-axis and `Segment` from the series. Line charts are set up to expect either one measure in the Y-Axis and one dimension in the Series OR multiple measures (one in the Y-axis and the others in Series). If you still have your segment in the series spot, it will not allow you to add more fields

pauljames

@lawiti , Hi! make sure you have this selected to see.

lawiti

@pauljames I have it selected but still not showing the zeros on the chart

pauljames

@lawiti , can you send pic of chart? It will help me see what needs to be done.

pauljames

@lawiti , try switching to grouped bar chart or nested bar to see total month too.

lawiti

@pauljames , thanks, I switched to grouped bar charts but still not showing the zeros, the data is a bit sensitive so I might not share here.

pauljames

@lawiti , oh. is the data showing as 'null' instead of 0? Change this in view or in beastmode.

lawiti

I tried this, IFNULL(SUM(Hires), 0), still not showing the zeros

pauljames

@GrantSmith @MarkSnodgrass @ColemenWilson @MichelleH

MichelleH

@lawiti If you are using a dimension field in the series, then your lines will be null instead of zero whenever there are no rows that meet that criteria. Instead of using Hires in the Y axis and a dimension in the series, try using a beast mode for each line like this:

sum(case when `Segment` = 'X' then `Hires` else 0 end)

trafalger

The other thing I've done is in a dataflow just appended a date dimension so there's a "row" for each date, even if there's no "actual" data. Only caveat is you need to make sure your blank date rows match your card filters otherwise they won't help.

lawiti

@MichelleH
Thank you for the suggestion, I tried this
SUM(CASE

WHEN `Segment` = 'A' THEN `Hires`

WHEN `Segment` = 'B' THEN `Hires`

WHEN `Segment` = 'C' THEN `Hires`

WHEN `Segment` = 'D' THEN `Hires`

WHEN `Segment` = 'E' THEN `Hires`

ELSE 0

END)
But still not showing the zeros, I have checked my data label setting, show when zero.

When I look at the dataflow output, I actually see the zeros,because this is how I calculated hires

Case when YEAR((Hire_Date)) = YEAR(CURDATE()) AND Employment_Status like 'Active' or Employment_Status like 'Leave' or Employment_Status like 'Active-Restricted Duty' or (Employment_Statuslike 'Terminated' and YEAR((Termination_Date)) = YEAR(CURDATE())and YEAR((Hire_Date)) = YEAR(CURDATE()) ) then 1 else 0 end

MichelleH

@lawiti You will need to make a separate beast mode for each segment, otherwise it will function the same as using just the `Hires` field. The reason you need individual beast modes is so that the formula evaluates every single row of data and return a result of 0 if the segment does not match the one you want, which then ensures that can see a zero value.

lawiti

@MichelleH , I have done that, but it seems I won't be able to use the line chart anymore?

MichelleH

@lawiti Be sure to remove `Hires` from the Y-axis and `Segment` from the series. Line charts are set up to expect either one measure in the Y-Axis and one dimension in the Series OR multiple measures (one in the Y-axis and the others in Series). If you still have your segment in the series spot, it will not allow you to add more fields

lawiti

@MichelleH , that works.Thank you!!

GrantSmith

I'm late to the party but another alternative would be to explicitly have a 0 record in your dataset for the months you don't have any hires.

pauljames

@GrantSmith , wouldn't my ifnull suggestion taken care of that though?

GrantSmith

Depends on the data and the chart being used. Some charts allow you to display missing dates on it but there actually isn't any data so it displays null. If the data actually has the dates and is set to null then your method would work.