topic Re: Referencing a different date within an expression in QlikView

Referencing a different date within an expression

paulm — Wed, 18 Mar 2015 20:27:50 GMT

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

Re: Referencing a different date within an expression

Wed, 18 Mar 2015 20:32:36 GMT

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

Re: Referencing a different date within an expression

hic — Wed, 18 Mar 2015 21:21:21 GMT

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

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

HIC

Re: Referencing a different date within an expression

paulm — Thu, 19 Mar 2015 09:11:31 GMT

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

Re: Referencing a different date within an expression

hic — Thu, 19 Mar 2015 09:25:26 GMT

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

Re: Referencing a different date within an expression

Thu, 19 Mar 2015 15:52:50 GMT

Can you upload a sample qvw?