Min Function not working with Date

[maahivee]

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.

[#2]

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