topic Date selections in QlikView

Date selections

Anonymous — Wed, 21 May 2014 18:40:55 GMT

I have a file that contains an Open Date, Closed Date, Account Balance, Account Status and other information about a customer's account. Open accounts will have an Open date but no Closed Date. Closed accounts will have both an Open and Closed Date.

I want to be able to use a single date selection to report the number of accounts opened and closed for a given week. NOTE: An account can be opened on Monday and closed on Wednesday - I should see 1 open account and 1 closed account recorded for this weekly date range. I already have the code figured out to select a weekly date range for either Open or Closed dates but not both at the same time.

Any thoughts as to how I can use both the Open and Closed dates as a single field that I can use for my date selection?

Thanks.

Re: Date selections

whiteline — Wed, 21 May 2014 19:07:24 GMT

Hi.

The data model that you use is object-based. You have accounts that have properties.

But your question is about the states of accounts on different dates.

You have to transform your data into states table that contains information about the account states for each date. Then you can use that date for calculations.

Or slightly more effective, you can store only the events (status changing) and use AsOfDate approach to get the status on each date.

Re: Date selections

Anonymous — Wed, 21 May 2014 19:08:26 GMT

See if this helps you:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Re: Date selections

Anonymous — Wed, 21 May 2014 19:43:31 GMT

Thanks Michael. My initial look at the link you referenced looks like it will work in this instance. It makes sense to load the date information a second time in order to facilitate the date selection process. I will pursue that solution.

Thanks again.