Count problem with date formating

[Anonymous]

Dear all,

I have a field (data_criacao) in a table with date format and I create a variable (DiaSelect) with the max vallue from the table field (MAX(data_criacao),

Now I want to count how many registers I have with the variable date (in this case the max valeu) but I´ll use it in a calendar to change it.

To do it I create a text box with the follow:

=count( if (date(data_criacao,'DD-MM-YYYY') = date(DiaSelect,'DD-MM-YYYY'), numero_pedido)) & '  -  ' &date(DiaSelect,'DD-MM-YYYY')   & '  -  '&max(date(data_criacao,'DD-MM-YYYY'))

and it returns    1  -  27-02-2012  -  27-02-2012    (in fact the base has 853 register in this condition)

I tryed : 

=count( if (Max(date(data_criacao,'DD-MM-YYYY')) = date(DiaSelect,'DD-MM-YYYY'), numero_pedido)) & '  -  ' &date(DiaSelect,'DD-MM-YYYY')   & '  -  '&max(date(data_criacao,'DD-MM-YYYY'))

and the expression is not OK.

Please can anyone help me.

Thanks in advance.

[swuehl]

Ok, it does count '1' value for field numero_pedidio. But does this value contain 853? Then you need to use sum() or only() or maybe nothing instead of count() function:

=sum( if (date(data_criacao,'DD-MM-YYYY') = date(DiaSelect,'DD-MM-YYYY'), numero_pedido)) & '  -  ' &date(DiaSelect,'DD-MM-YYYY')   & '  -  '&max(date(data_criacao,'DD-MM-YYYY'))

Maybe I am totally wrong here, just assuming that your registers are already aggregated in field numero_pedido.

[Anonymous]

Swuehl,

What I´m trying is to count how many ORDERS (numero_pedido) I have for a specific date.

So, I can not use ONLY  or SUM functions.

The variable DiaSelect brings the las order day as an initial parameter to count.

The problem is when I use the MAX funciont into the IF function in the expression bellow.

=count( if (Max(date(data_criacao,'DD-MM-YYYY')) = date(DiaSelect,'DD-MM-YYYY'), numero_pedido)) & '  -  ' &date(DiaSelect,'DD-MM-YYYY')   & '  -  '&max(date(data_criacao,'DD-MM-YYYY'))

[swuehl]

You can't embed an aggregation function into another aggregation function (Max() into count() ). That's not possible.

You need to use advanced aggregation using aggr() function.

It seems I don't understand your setting properly. Would it be feasible that you upload a small sample, or some INLINE table data?

[Anonymous]

The tabel format is simple as bellow:

data_criacao     numero_pedido                    (data_criacao means Order date and numero_pedido means order #)

20-12-2012          1232

20-12-2012          1238

20-12-2012          1237

21-12-2012          1236

22-12-2012          1235

22-12-2012          1234

I create a variable DiaSelect with MAX(date(data_criacao,DD-MM-YYYY)  and in this example will have '22-12-2012' as a value.

I will use a calendar to show the variable and a text box to show a count of order in a variable day. The count is my problem.

Thanks again.

[swuehl]

With your above sample data and using your expressions, I get a count of 2. Seems correct to me, since you have two records for 22-12-2012.

Using a calendar, I can select 20-12-2012, 21-12-2012 or 22-12-2012 and I get 3,1 and 2 as counts.

[Anonymous]

Yes, but the expression comes allways with 1 as a result. It is not counting well.

[swuehl]

As I said, using your expressions I get results 3, 1 and 2 for those three different dates.

Seems ok to me (with your sample data, maybe your real data differ in structure?)

[Anonymous · Accepted solution]

Swuehl,

Found the problem. The field format after date (xxx,'DD-MMM-YYYY') does not cut the time after the date, so the counter will allways be one.

I included a left(xxxx,10) and it worked well.

Thanks the help.