topic Re: Count problem with date formating in QlikView

Count problem with date formating

Anonymous — Thu, 08 Mar 2012 20:39:18 GMT

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.

Count problem with date formating

swuehl — Thu, 08 Mar 2012 20:56:48 GMT

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.

Count problem with date formating

Anonymous — Thu, 08 Mar 2012 22:53:01 GMT

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'))

Count problem with date formating

swuehl — Thu, 08 Mar 2012 23:05:04 GMT

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?

Count problem with date formating

Anonymous — Thu, 08 Mar 2012 23:15:31 GMT

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.

Count problem with date formating

swuehl — Thu, 08 Mar 2012 23:30:38 GMT

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.

Count problem with date formating

Anonymous — Thu, 08 Mar 2012 23:36:25 GMT

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

Re: Count problem with date formating

swuehl — Thu, 08 Mar 2012 23:47:00 GMT

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?)

Count problem with date formating

Anonymous — Fri, 09 Mar 2012 22:09:38 GMT

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.