Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
paulm
Contributor III
Contributor III

Referencing a different date within an expression

HI,

I have a list of calls that have associated created (CallDate) and closed (ClosedDate) dates.  I am wanting to see what is closed on a certain date – with the chart dimension based on CallDate.  I have wrote the following but it is only returning calls that are closed on the same day.  Any help on how to complete this?

sum(aggr(count(DISTINCT{<CallDate=,ClosedDate={'=CallDate'}>}CallID),CallDate)) 

Thanks

Paul

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

Not sure I understand...

If you use CallDate as dimension, then the count of CallIDs will pertain to the CallDate, no matter how you define your condition. But if you instead want to see the number of calls closed on a certain date, you should use CloseDate as dimension.

If you want to show both along the same date axis, you should read Canonical Date.

HIC

View solution in original post

5 Replies
Not applicable

You may have to use the Alternate Sates. Check this out... http://community.qlik.com/thread/51147

hic
Former Employee
Former Employee

Make a chart with CallDate as dimension and the following as measure:

  Count(distinct If(ClosedDate<CallDate,CallID))

HIC

paulm
Contributor III
Contributor III
Author

Hi

Thanks for your replies!

srukmana - I have not did much with alternative states, I will have a play and see how I get on.  Would you just create an alternative state for the closedDate and reference it within the chart or would it all need to be in a different state?  I just want to make sure we do not lose the functionality of all the other selectors.

Henric, Thanks for the reply, unfortunately not exactly what I am after.  I am wanting to find those that were closed on a certain date, but also not limited to calls that were raised on the date.  The problem I am having is the data model is based on the call raised date and was trying to ignore it even though it is the date of the dimension, and return those calls closed on that date.

Thanks

Paul

hic
Former Employee
Former Employee

Not sure I understand...

If you use CallDate as dimension, then the count of CallIDs will pertain to the CallDate, no matter how you define your condition. But if you instead want to see the number of calls closed on a certain date, you should use CloseDate as dimension.

If you want to show both along the same date axis, you should read Canonical Date.

HIC

Not applicable

Can you upload a sample qvw?