topic Picking first date in multiple date set in QlikView

Picking first date in multiple date set

Thu, 11 Aug 2011 17:42:50 GMT

I have a date field imported as: cast(date.date_full as date) AS [Discharge Date] . When I pick a date range within QlikView such as Jan. 1st to March 31st, I want to pick just the first date for a table. ie. Jan 1st, 2011. What should the expression look like? I would think it would be something like: min([Date Discharge]) or first([Date Discharge]), but these are not correct... Can someone please tell me how to do this? Thanks.

Re: Picking first date in multiple date set

johnw — Thu, 11 Aug 2011 17:59:12 GMT

Min([Date Discharge]) gives you the minimum date possible based on your current selections, but what do you mean by "pick... for a table"? You mean, for example, have a table by customer of the sum of sales for just that minimum date? That would end up as an expression something like this:

sum({<[Date Discharge]={'$(=min([Date Discharge]))'}>} Sales)

What you need depends on your requirement.

Re: Picking first date in multiple date set

swuehl — Thu, 11 Aug 2011 18:00:39 GMT

Hi,

could you please explain what the cast() expression does? Couldn't find it in the manual.

Then I assume it's a typo here in the forum that you use [Discharge Date] in the load and [Date Discharge] for the min, right?

min([Date Discharge]) should work if your Date is really a Date type / numerical.

To limit your data, you could use a Date as dimension and then e.g. sum(if (Date=min([Date Discharge]), value))

Regards,

Stefan

edited expression

edited by swuehl

Picking first date in multiple date set

Thu, 11 Aug 2011 18:52:30 GMT

Yes I meant to type min([Discharge Date]) or first([Discharge Date]) above.... however, that is not the problem just a typo.... when I do min([Discharge Date]), I get a number like 40504... instead of the date like 1/1/2011.... any help would be appreciated..

Re: Picking first date in multiple date set

johnw — Thu, 11 Aug 2011 19:02:52 GMT

40504 is the internal representation of the date, the number of days since December 30, 1899. It sounds like your discharge date is not properly formatted. Document Propterties -> Numbers -> Discharge Date -> select "Date" -> checkmark "Survive reload". That should work, as min() should preserve your date formatting. For a sum(if()), you don't even need it formatted - QlikView will compare the internal representations, and couldn't care less how you choose to format it for display. For set analysis like I posted, which compares the formatted value, if all else fails you may need to explain to QlikView again that it's a date:

sum({<[Discharge Date]={'$(=date(min([Discharge Date])))'}>} Sales)

That shouldn't be necessary, though, as long as your date field is properly formatted.