Skip to main content
Question

Dashboard / Studio Question: How to create a histogram chart with all calendar months visible on X-axis, even if no activity is available for a particular month.

  • Apr 14, 2022 2:30 PM UTC
  • 3 replies
  • 96 views
Level 1

I'd like to create a histogram that shows me the distribution of certain activities across a calendar year (Jan to Dec), independent of the year it happened. In other words, if such an activity happened in March 2019 and March 2021, it would count as "2" for March. This simple chart works fine if I have at least one activity for each month of the calendar year, like shown here.

 

However, if I don't have it, like in this case, the chart 'falls apart', like here:

I was wondering if one of you had an idea on how I could 'lock in' such a 12 month x axis.

(Icing on the cake would be if I could show month names, like Feb, instead of numbers, like 2)

 

Thanks!

    3 replies

    • Anonymous
    • Apr 15, 2022 1:05 AM UTC

    Hi Axel,

     

    Try this for your dimension axis:

     

    MONTH(RANGE_APPEND ( ROUND_MONTH ( "Table"."DateColumn" ) , '1M' ))

     

    To just display the month name, use %B in the formatting field for full month name, or %b for shortened month name.

     

    regards

    David

    Level 1
    • axel.demut
    • Author
    • Level 1
    • Apr 15, 2022 12:47 PM UTC

    Hi David,

    Thank you so much for your post. The formula worked like a charm!

     

    image 

    However, I could use a bit more hand-holding with how to apply the format (%B). I tried different spots and unfortunately, I am still not clear even after studying the Celonis help menu once more.

    imageimage 

    => Where does this %B or %b need to go?

     

    Thanks and sorry for being so dense :-)

    Level 1
    • axel.demut
    • Author
    • Level 1
    • Apr 18, 2022 12:51 PM UTC

    I think I found a solution - it's a bit brute force though...

     

    REMAP_VALUES(TO_STRING(MONTH(RANGE_APPEND ( ROUND_MONTH ( "Table"."DateColumn" ) , '1M' ))),  ['1','01_Jan'],['2','02_Feb'],['3','03_Mar'],['4','04_Apr'],['5','05_May'],['6','06_Jun'],['7','07_Jul'],['8','08_Aug'],['9','09_Sep'], ['10','10_Oct'],['11','11_Nov'],['12','12_Dec'] )

     

    image

    Community Code of ConductAccessibility statement