Creating combo chart with 12 month period

[Maciej29]

Hello I'm looking for way how to built formula to get combo chart divided by month, for 12 months. Also would like to count dates on hold for every month in bars, and use line to present average Hold Aging on the same chart. Could you help me to create working formula ?

Please see table in .xlsx attachment.

[Rodj · Accepted solution]

Hi @Maciej29 ,

There's quite a few ways you could go about this but I'll try to give you the most straight forward.

In your visualisation (table, chart etc) create a dimension for your months. In the chart properties add a dimension and in the expression editor put: "Month([Date on Hold])" and give the dimension the label "Month". We are calculating the dimension we want from the date field that you already have.

Next you need to add your two measures in much the same way. You can do this by just dragging the appropriate fields onto the table/chart or you can manually enter them via the chart properties as we did with the dimension. Drag the "Date on Hold" field onto your chart from the field list on the left of screen and nominate to add it as a measure and select the "count" option. Do the same for the "Hold Aging" field but this time select average.

There's lots of great info in the online help that can help get you started with the basics like this, including tutorials you can work through that will cover all of what I've just described in  more detail.

Good luck,

Rod

[Maciej29]

Thank you Rodj,

Could you help me modify my formula to get year/month sequence (from earliest to latest)?

=Year([Date Finance Hold])&'-'&Month([Date Finance Hold])

[Rodj]

Hi Macie,

Perhaps the quickest way is to use the sort "by expression" option in the sorting properties for the chart. For the Year-Month field that you have, check the "sort by expression" option and then in the expression put a slightly different expression such as this one:

=Date([Date Finance Hold], 'YYYYMM')

That will give you a simple year month number sequence that is easily sorted ascending or descending as desired.

Keep in mind that is just one (fairly straight forward) way of doing this, you'll find a few other methods that people have used throughout community.

Cheers,

Rod