Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Expression
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
Expression
Hope that helps.
Kind regards
Steve