topic Re: Chart expression - closed dates in QlikView

Chart expression - closed dates

Sat, 03 Dec 2016 15:02:11 GMT

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

Re: Chart expression - closed dates

Anonymous — Mon, 05 Dec 2016 06:36:14 GMT

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

Re: Chart expression - closed dates

Gysbert_Wassenaar — Mon, 05 Dec 2016 09:31:39 GMT

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).

Re: Chart expression - closed dates

Wed, 07 Dec 2016 17:42:58 GMT

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

Re: Chart expression - closed dates

Gysbert_Wassenaar — Wed, 07 Dec 2016 18:13:49 GMT

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.

Re: Chart expression - closed dates

Wed, 07 Dec 2016 22:26:46 GMT

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.

Re: Chart expression - closed dates

Gysbert_Wassenaar — Thu, 08 Dec 2016 06:13:00 GMT

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.

Re: Chart expression - closed dates

Thu, 08 Dec 2016 08:52:43 GMT

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.