date as a text

[#1]

hi all,

sorry for my english, i speak spanish...

i´m using crosstable and the columns are dates. When i see it on QV are in text format. If i export to excel i see as date correctly. here i send excel and script. What can i do to see dates as date format.

Excel

U	01/01/2009	01/02/2009	01/03/2009
Q	50.309	50.309	50.309
Q	50.309	50.309	50.309
Q	50.146	50.146	50.146
Q	64.314	64.314	64.314

Script

Budget:

CrossTable

(PERIOD, BUDGET, 2)

LOAD

* FROM (biff, embedded labels, header is 1 lines, table is [Budget Ext$] );

THANKS

pablo

[#2]

Hi,

Try this. Add a calculated dimension with:

=date(<date filed>,'DD/MM/YYYY')

Have Fun

Alex:)

[#3]

thanks Alex, but it doesn´t work. All values are null now.

pablo

[#4]

i think there´s a problem with the cross table; filds called DATE are taken as text and i can´t show them as date.

i don´t know if it´s allow to write somethig when i load the script to format this field as date.

thanks

[#5]

Hi,

try

=date#(<date filed>,'DD/MM/YYYY')

or

=date(date#(<date filed>,'DD/MM/YYYY'),<Formt code>)

if this doesn't work, you could post an example and we'l see.

Have Fun

Alex:)

[#6]

Hi Alex, thak you very much again...it doesn´t work...

this is the excel:

Sign	Group	01/01/2009	01/02/2009	01/03/2009	01/04/2009	01/05/2009	01/06/2009	01/07/2009	01/08/2009	01/09/2009	01/10/2009	01/11/2009	01/12/2009
a	1	45636	45646	45666	45696	45736	45786	45846	45916	45996	46086	46186	46296
a	1	45315	45325	45345	45375	45415	45465	45525	45595	45675	45765	45865	45975
a	1	15313	15323	15343	15373	15413	15463	15523	15593	15673	15763	15863	15973
a	1	35413	35423	35443	35473	35513	35563	35623	35693	35773	35863	35963	36073
a	2	35416	35426	35446	35476	35516	35566	35626	35696	35776	35866	35966	36076
b	2	48316	48326	48346	48376	48416	48466	48526	48596	48676	48766	48866	48976
b	2	84316	84326	84346	84376	84416	84466	84526	84596	84676	84766	84866	84976
b	3	84132	84142	84162	84192	84232	84282	84342	84412	84492	84582	84682	84792
c	3	41385	41395	41415	41445	41485	41535	41595	41665	41745	41835	41935	42045
c	3	52241	52251	52271	52301	52341	52391	52451	52521	52601	52691	52791	52901
c	2	48135	48145	48165	48195	48235	48285	48345	48415	48495	48585	48685	48795
a	1	84135	84145	84165	84195	84235	84285	84345	84415	84495	84585	84685	84795
a	2	48315	48325	48345	48375	48415	48465	48525	48595	48675	48765	48865	48975
a	3	54121	54131	54151	54181	54221	54271	54331	54401	54481	54571	54671	54781
b	1	15613	15623	15643	15673	15713	15763	15823	15893	15973	16063	16163	16273
b	2	18661	18671	18691	18721	18761	18811	18871	18941	19021	19111	19211	19321
c	1	18613	18623	18643	18673	18713	18763	18823	18893	18973	19063	19163	19273
c	3	18411	18421	18441	18471	18511	18561	18621	18691	18771	18861	18961	19071

i create a crosstable whit the dates and when i make a date table it appears like this...

39814

39845

39873

39904

39934

39965

39995

40026

40057

40087

40118

40148

if i export the table to excel i see it OK, but at QV it´s a text only...i´ve tried what you said but nothing.

thaks,

pablo.

[#7]

Pablo,

I'm looking into this as well...I'll post up something soon.

[#8 · Accepted solution]

Pablo,

I got the answer from one of our Support gurus after trying it it a few times myself:

The values are being interpreted as straight text which means that they won't respond to a date() function which works on a numeric level.You can tell QV to interpret the values numerically by using Num#(). Combining the two should give you proper dates:

date(num#(Period)) as Period

See the attached QVW file for reference. I'm marking this one complete, please let me know if you have any more questions.

[#9]

Hi Jason, thak you!!! it worked.

Thank you Alex too. i hope to help you soon.

pablo.

[#10]

A mi me pasó lo mismo y no me sirvió nada mas que esto (Evaluate):

Map_Extradata:

Directory;

CrossTable(YourDate, Data,3)

LOAD * FROM

[\\telesto\Base para QV\Balance & Cta. Resultados\Datos adicionales resultados.xlsx]

(ooxml, embedded labels, table is Plano);

LOAD

Fila,

Concepto,

Empresa,

Date(Num(Evaluate(YourDate) )) as "Posting Date",

Datos as Amount

Resident Map_Extrasata;

Drop Table Map_Extradata;