topic Re: Min Function not working with Date in QlikView

Min Function not working with Date

maahivee — Wed, 25 Nov 2020 16:16:04 GMT

Hello Guys , I Have a small issue in my dashboard.. In the straight table I am trying to add Min function for Date.

My requirement is like this.. Each order may have multiple invoices. I am trying to show the 1st invoice for each order.

So i am trying to give the calculated dimension like this - If( Date = Min(Date) , Date). Also giving "Suppress when value is null" for that particular dimension.

So, the date here is the invoice date, So when I use the above statement I would expect it to show only the 1st invoice for every order.

But instead it is just giving "error in calculated dimension".. Can you guys please help me here..

This one is so simple and yet so stressful by not working..

Any help here is highly appreciated.. Thank You.

Re: Min Function not working with Date

sunny_talwar — Thu, 07 Apr 2016 19:05:06 GMT

Try this:

If(Date = Min(TOTAL Date) , Date)

Re: Min Function not working with Date

maahivee — Thu, 07 Apr 2016 20:11:17 GMT

Thank you for the reply.. But its giving out null all thru.

Re: Min Function not working with Date

Colin-Albert — Thu, 07 Apr 2016 20:11:23 GMT

Is your DATE field correctly loaded as a dual field?

If you put a list box on your DATE field are the values left justified indicating they are text, or right justified indicating a dual field.

Min() returns the numeric minimum and should be used on date fields. Minstring() should be used on text data.

This blog has more details on dates in Qlikview. Get the Dates Right

Re: Min Function not working with Date

sunny_talwar — Thu, 07 Apr 2016 20:14:04 GMT

I guess you would then need to check if you date is actually read as date by QlikView or not -> Why don’t my dates work?

Re: Min Function not working with Date

maahivee — Thu, 07 Apr 2016 21:01:18 GMT

Yes have tried multiple ways.. Like with Min(Num(Date))..

But when loading the date i a doing it like this - Date(Floor(Date))

Re: Min Function not working with Date

sunny_talwar — Thu, 07 Apr 2016 21:03:16 GMT

May be give this a shot:

Aggr(Only({1<Date = {'$(=Date(Min(Date)))'}>} Date), Date)

Re: Min Function not working with Date

swuehl — Thu, 07 Apr 2016 21:07:34 GMT

maahivee wrote:

Yes have tried multiple ways.. Like with Min(Num(Date))..

But when loading the date i a doing it like this - Date(Floor(Date))

Ok, but can you confirm that your dates have a numeric representation?

Do you get numbers when you are creating a table chart with dimension Date and expression =Num(Date)

Re: Min Function not working with Date

swuehl — Thu, 07 Apr 2016 21:21:10 GMT

Ah, I missed that you are using multiple dimensions, Order and Invoice:

=Aggr( If( Date = Min(TOTAL<Order> Date), Date), Order, Date)

Re: Min Function not working with Date

maahivee — Fri, 08 Apr 2016 00:37:02 GMT

Thank you all guys for all your time and effort..

Re: Min Function not working with Date

Fri, 08 Apr 2016 00:38:29 GMT

Hi Maahivee

Here is an alternative approach to your problem.

Rather than use the chart to calculate the first invoice number, which incurs a memory overhead as the chart is calculated, you could instead calculate which is the first invoice for each order in the script. This would allow you to use a list box to select just the first invoice from each order.

The invoice numbers must be numeric for the min function to work.

As an example, here is some sample code:

Data:

load * inline [

recID,orderNumber,invoiceNumber,invoiceDate,orderValue

1,1,1,01/02/2016,56

2,1,2,03/02/2016,120

3,1,3,08/02/2016,126

4,2,1,02/02/2016,26

5,2,2,02/02/2016,56

6,2,3,02/03/2016,34

7,2,4,02/04/2016,78

8,2,5,02/05/2016,234

9,3,1,02/04/2016,879

10,3,2,12/03/2016,45

11,3,3,02/04/2016,123

12,3,4,08/04/2016,2436

13,3,5,10/04/2016,67

14,3,6,12/04/2016,87

15,3,7,16/04/2016,345

16,4,1,07/06/2016,978

17,4,2,02/07/2016,23

18,4,3,16/08/2016,56

19,4,4,05/09/2016,42

20,4,5,02/10/2016,234

21,4,6/12/2016,978

22,4,7/12/2016,2665];

left join (Data)

Load

orderNumber

,min(invoiceNumber) as firstinvoice

Resident Data

Group By orderNumber

;

left join (Data)

Load

recID

,If(invoiceNumber=firstinvoice,'First',) as firstInvoiceIndicator

Resident Data

;

Once you have loaded the data create a straight table with:

Dimensions

orderNumber
invoiceNumber
invoiceDate

Expression

sum(orderValue)

Then add a list box and choose the 'firstInvoiceIndicator' dimension..

What this does is to firstly identify the first invoice for each order then flag this invoice number record in a new dimension. By adding the 'firstInvoiceIndicator' dimension to a listbox you will be able to select the value of 'First' to instantly filter your records to just the first invoice for each order or release the filter to see them all, effectively creating a toggle option.

If you only ever want to see the first invoices you could then either not load the data you don't need, or use the 'firstInvoiceIndicator' dimension in a calculated dimension

e.g.

Once you have loaded the data create a straight table with:

Dimensions

=If(firstInvoiceIndicator='First',orderNumber,)
- And tick Suppress When Value Is Null
invoiceNumber
invoiceDate

Expression

sum(orderValue)

Hope that helps.

Kind regards

Steve