Chart expression - closed dates

[#1]

Hi

I have an app that reports on closed incidents on a IT helpdesk. One bar chart has 2 expressions and a dimension of calendar month:

Expression1. all incidents open in a given month so COUNT(CreatedDate)

Expression2. all incidents closed in a given month. I cannot use COUNT(ClosedDate) because I will get a count of 1 for a record even if the closed date equals the following month so I need a different expression for this one.

Any idea's?

Thanks

[Anonymous]

Hi Nick,

Usually , this type of report goes out at the end of every month. Please ensure you reload the application after the month ends.

Best regards,

Kaveri

[Gysbert_Wassenaar · Accepted solution]

My guess is you need another dimension for this. You're probably using a dimension based on CreatedDate. You need to remodel your data so you have a date dimension that can be used for counting both opened and closed calls.

CallsTimeLine:

LOAD

     CallID,

     CreatedDate as Date,

     'Created' as DateType

     1 as CreatedCount

    ...perhaps some other fields...

FROM

     ...sourcedata...

WHERE

     Len(Trim(CreatedDate))>0

     ;

CONCATENTE (CallsTimeline)

LOAD

     CallID,

     ClosedDate as Date,

     'Closed' as DateType

     1 as ClosedCount

    ...perhaps some other fields...

FROM

     ...sourcedata...

WHERE

     Len(Trim(CreatedDate))>0

     ;

You can then use the common Date field as dimension and expressions like sum(CreatedCount) and sum(ClosedCount).

[#4]

Hi Gysbert,

Am I right in assuming the original table that holds these fields will simply be concatenated with its self whilst creating the new field types and then dropping? This will not be an island table linking to the original table it will take its place, is that what you mean by remodel my data? Thanks

[Gysbert_Wassenaar]

It creates a new table. You can decide for yourself if you want the one you created as well. The two tables would be associated on the common fields. An identifier like CallID sounds like a good candidate.

[#6]

I have kept both tables linked with the ID, I have then used the DATE from the new table as the dimension in my bar chart but the results do not tally evenly for given months, they actually spread across many months in some cases. My Calendar is still based on the CreatedDate from the Incident table.

Even if I did use only the new table my Calendar would still be based on this so cannot see how this will work whilst using my main calendar.

[Gysbert_Wassenaar]

If you base your main calendar on the CreatedDate field then you'll never get the ClosedDate counts right. That's another dimension. Any ClosedDate is a value in a record that also has a CreatedDate. And that CreatedDate is the dimension you're using as x-axis in your chart.

So you need to base your calendar on the Date field of the new table.

[#8]

Sorry Gysbert, I figured this out not long after posting my reply. Just verifying the results now and adding a few additional fields. I see what you mean now when you say remodel my data.