Displaying Zero Hires by Month for Different Segments in Line Charts

lawiti
lawiti Member
edited November 2023 in Charting

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?

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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

Answers

  • pauljames
    pauljames Contributor
    edited November 2023

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

    image.png

    IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

  • lawiti
    lawiti Member

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

  • pauljames
    pauljames Contributor

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

    IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

  • pauljames
    pauljames Contributor
    edited November 2023

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

    image.png

    IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

  • lawiti
    lawiti Member

    @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
    pauljames Contributor

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

    image.png
    image.png

    IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

  • lawiti
    lawiti Member

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

  • pauljames
    pauljames Contributor

    @GrantSmith @MarkSnodgrass @ColemenWilson @MichelleH

    IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

  • MichelleH
    MichelleH Coach

    @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
    trafalger Coach

    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
    lawiti Member
    edited November 2023

    @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
    MichelleH Coach

    @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
    lawiti Member

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

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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
    lawiti Member

    @MichelleH , that works.Thank you!!

  • GrantSmith
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • pauljames
    pauljames Contributor

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

    IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

  • GrantSmith
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories