topic Sum between two date in QlikView

Sum between two date

Tue, 13 Apr 2010 15:48:40 GMT

Hi, I´m new in QV and I have this problem.

I have a table with a few columns, I need to generate a graph that sum the Column AQN_Vol_Baseline_IVA if the column AQN_Fecha_R_Adj.Day is between 1/1/2010 and 31/3/2010. The first field is a number and the second is a date.

Thanks so much.

Sum between two date

Tue, 13 Apr 2010 16:03:37 GMT

hi,

try something like this:

sum({$<AQN_Fecha_R_Adj.Day= {">= 1/1/2010 <= 31/3/2010"}>} AQN_Vol_Baseline_IVA )

Alex

Sum between two date

Tue, 13 Apr 2010 16:49:02 GMT

Alex, thaks so much for your quick answer, I think I´m doing something wrong....I change some field names but I can show you what's going on:

sum({$<date([Fecha Real de Adjudicación])= {">= 1/1/2010 <= 31/3/2010"}>} [Volumen Baseline Año 1] )

I detect that the field [Fecha Real de Adjudicación] is not in the format 1/1/2010 and I use date([Fecha Real de Adjudicación]) but something is wrong...

Fecha Real de Adjudicación	date ([Fecha Real de Adjudicación])	sum({$<date([Fecha Real de Adjudicación])= {">= 1/1/2010 <= 31/3/2010"}>} [Volumen Baseline Año 1] )

40226	17/02/2010
40242	05/03/2010
40207	29/01/2010
40206	28/01/2010
40228	19/02/2010
40231	22/02/2010
40218	09/02/2010
40262	25/03/2010
40255	18/03/2010
40225	16/02/2010
40213	04/02/2010
40219	10/02/2010
40248	11/03/2010
40240	03/03/2010
40249	12/03/2010
40260	23/03/2010
40247	10/03/2010
40232	23/02/2010
40259	22/03/2010
40224	15/02/2010
40221	12/02/2010
40252	15/03/2010
40256	19/03/2010
40238	01/03/2010
40241	04/03/2010
40227	18/02/2010
40235	26/02/2010
40268	31/03/2010
40266	29/03/2010
40233	24/02/2010
40267	30/03/2010
40276	08/04/2010
40263	26/03/2010
40280	12/04/2010
40277	09/04/2010

Sum between two date

Tue, 13 Apr 2010 20:26:27 GMT

Alex, I really appreciate if you could see my new message, regards.

Sum between two date

johnw — Tue, 13 Apr 2010 23:38:17 GMT

So far as I know, you can't use set analyis on expressions, only on fields. In other words, I don't think you can use a function on [Fecha Real de Adjudicación] inside of set analysis. You CAN specify expressions in the search expression using dollar sign expansion, but that's completely different.

My suggested solution would be to format [Fecha Real de Adjudicación] as a date either with a date() function in the load, or by going to document properties, number, select the field, select "Date" as the format, specify the format pattern, and checkmark "survive reload". Then simplify your expression as follows:

sum({<[Fecha Real de Adjudicación]={">= 1/1/2010 <= 31/3/2010"}>} [Volumen Baseline Año 1])

If you WANT to keep it formatted as a number most of the time in your document, you will have to convert your two dates to the same numeric format. If they are literals like you show, just use the equivalent numeric literals:

sum({<[Fecha Real de Adjudicación]={">= 40179 <= 40268"}>} [Volumen Baseline Año 1])

Sum between two date

Thu, 15 Apr 2010 00:41:13 GMT

John, thanks so much. It works great.