topic Re: Find earliest invoice/create date in QlikView

Find earliest invoice/create date

Anonymous — Wed, 15 Apr 2015 15:29:29 GMT

Trying to display only ONE invoice with the earliest date per supplier.

I have made a chart with supplier as the dimension and the expression is MIN(DATE) but that is not producing one record.

Any suggestions?

Re: Find earliest invoice/create date

MK_QSL — Wed, 15 Apr 2015 15:35:58 GMT

It should work... If not working means your DATE format is wrong...

Just create a Text Box and write below expression

=Min(Date)

If you are not getting numeric value something like 40892, you need to format the Date properly.

In that case, you can use as below

=Date(Date#(Date,'MM/DD/YYYY'))

Here I have considered that your Date Format is MM/DD/YYYY but you need to change it according to what your database has..

Re: Find earliest invoice/create date

robert_mika — Wed, 15 Apr 2015 15:37:58 GMT

This configuration works for me:

Could you post yours?

Maybe is a problem with Date format.

Re: Find earliest invoice/create date

Anonymous — Wed, 15 Apr 2015 15:43:58 GMT

Manish/Robert - The MIN function works if I am only considering the SUPPLIER NUM/NAME dimension. However, I need other dimensions from this record, such as INVOICE NUMBER, AMT, etc ...

I guess a better way to put my requirement is:

I need to display several dimensions for one SUPPLIER where the DATE equals the MINIMUM value.

In lehmans terms what I am trying to do is find the first invoice record created for a vendor in our system

Re: Find earliest invoice/create date

MK_QSL — Wed, 15 Apr 2015 15:47:51 GMT

May be try below... Or provide some sample data...

Min(Total <Supplier> Aggr(Min(Date),Supplier))

Re: Find earliest invoice/create date

Wed, 15 Apr 2015 17:31:21 GMT

Try using dimension limits and you can chose the required filter over there.

Thanks,

Sai

Re: Find earliest invoice/create date

Anonymous — Wed, 15 Apr 2015 17:32:53 GMT

Aggr(Min(CREATION_DATE),SUPPLIER_NUM) worked for Manish. Thanks for helping me get there