Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Chart expression - closed dates

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

7 Replies
kaveri_bhar123
Contributor II
Contributor II

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

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


talk is cheap, supply exceeds demand

View solution in original post

Not applicable
Author

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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.