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.
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:
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..
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: