Referencing a different date within an expression

[paulm]

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

[#2]

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

[hic]

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

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

HIC

[paulm]

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 · Accepted solution]

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

[#6]

Can you upload a sample qvw?