Stacked Bar Charts with 2 'date' counts per month

Hi,

Thanks for reaching out. I don't have your data, so the exact methods to get to the same point may differ, but I think the following is a good place to start.

Here's what I tried: click the arrow next to your "Create Date" and "Closure Date" columns and use 'Aggregation' > 'Count' for each one. Then, add a "Month" column in your Data tab. Next, go to your Charts tab and add the "Count Create Date" and "Count Closure Date" Metrics to your Vertical Axis, and "Month" to your Horizontal Axis:

I happen to have the same Counts for Start Date and End Date for each month, so your chart may look a little different.

Perhaps you can give this method a try and see what you come up with. Let me know how that goes.

Thanks,

Mike

Thanks Mike. It hasn't quite worked out, but my dataset and environment is not as simple as I would like. I also get the same counts. even though I know it's wrong. I think I might just have to accept that I'll need 2 separate reports and work around it with Excel.

Cheers.

Hi Andrew,

I believe I figured out how to accomplish this:

Go into your view and click on the "+" sign and Create a Date Function. Select your Field, Date Function, Format, and Field Folder.

Repeat these steps for the Closure Date.

Click the 3 Dots next to the new functions and convert the Field Types to Dimensions.

Next, Drag in your new Creation Date column in the report builder. Then create an Append Sub Query, and make Master Query Field your new Creation Date function = Sub Query Field your new Closure Date function.

In the Master Query tab, put your original Create Date tab, and in the Append 1 tab, put your Closure Date tab, then 'Aggregation' > 'Count' both columns, now you should have the correct values for each:

Also note the counts won't be correct until BOTH are aggregated.

Now we have a correct Vertical Stacked Chart:

Hopefully this gets this working for you. Let me know how that goes.

Regards,

Mike

Thanks Mike, that looks really good! My version of this product doesn't appear to have the date functions that you've referred to. I guess I'll have to wait for my version to be upgraded.

In the meantime, I'll bookmark this page and come back to it when we're compatible :)

Thanks a lot for your efforts with this. It's certainly not as easy as I thought when I started!

Hi Andrew,

Thanks for your response. I'm right with you on this one! Haha. That said, this is available in 7.3 as well. To follow these steps you have to look at your data from the View level, not from the report builder. So you'd start by going 'Browse' > 'Views' > 'Prepare', then follow the steps in my previous post to create a date function. Let me know how that works for you, and as always, if you have further questions I'd be happy to help.

Regards,

Mike

Hi Andrew,

Just wondering how things are going with this and if you are requiring further assistance.

Regards,

Mike

Hi Andrew,

I'm just going through some of my older cases. I'm going to go ahead and mark this one as Answered, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.

Regards,

Mike