4 Replies Latest reply: Aug 11, 2011 3:02 PM by John Witherspoon RSS

    Picking first date in multiple date set

      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
          John Witherspoon

          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
            Stefan Wühl

            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

              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
                  John Witherspoon

                  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.