only(Year)-1 doesn't work

[Anonymous]

Hi!

I'm struggling  with a problem that i think has something to do with the format of Year.

I'm loading a crosstable like this;

Data:

CrossTable(Year, Amount, 5)

LOAD [Company],

     Distributor

     Code,

     Family,

     Category,

     [2008],

     [2009],

     [2010]

FROM

(ooxml, embedded labels, header is 1 lines, table is DATA);

I've tried to change the format in the excel file for year but it doesn't change. I'va also tried to change it in the document settings as well but no luck.

Hence, this expression does not work;

=Only(Year)-1

Please Help

( =Only(Year) works fine though..)

[kaushiknsolanki]

Hi,

    Try this

     =Only(Num(Year,'###0')-1)

Regards

Kaushik Solanki

[IAMDV]

Have you tried the same function on Preceeding load or resident load? Instead of using it directly on CrossTable...

Cheers - DV

[Anonymous]

Didn't work. Nothing shows unfortunatly.

@DV: I tried this;

Load *,

     num(Year, '###0') as Year2

Resident Crosstable;

Drop Crosstable;

But the new field (Year2) is completely empty when i look at it in the GUI.

[kaushiknsolanki]

Hi,

    Can you please send the QVW file and the Excel file

Regards,

Kaushik Solanki

[IAMDV]

Please can you upload the excel file with sample data? It will be easy for me to work on the script.

Thanks - DV

[Anonymous]

Here you go,

Thanks.

[#8]

Hi,

see the attached example.

Good luck!

Rainer

[IAMDV · Accepted solution]

Please use this function Num#(MyYear) -1 and it should work.

[Anonymous]

Thank you! It worked!

//A.