topic only(Year)-1 doesn't work in QlikView

only(Year)-1 doesn't work

Anonymous — Thu, 19 May 2011 08:12:15 GMT

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

only(Year)-1 doesn't work

kaushiknsolanki — Thu, 19 May 2011 08:16:42 GMT

Hi,

Try this

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

Regards

Kaushik Solanki

Re: only(Year)-1 doesn't work

IAMDV — Thu, 19 May 2011 08:17:51 GMT

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

Cheers - DV

Re: only(Year)-1 doesn't work

Anonymous — Thu, 19 May 2011 08:24:25 GMT

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.

Re: only(Year)-1 doesn't work

kaushiknsolanki — Thu, 19 May 2011 08:26:09 GMT

Hi,

Can you please send the QVW file and the Excel file

Regards,

Kaushik Solanki

Re: only(Year)-1 doesn't work

IAMDV — Thu, 19 May 2011 08:26:43 GMT

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

Thanks - DV

Re: only(Year)-1 doesn't work

Anonymous — Thu, 19 May 2011 08:34:52 GMT

Here you go,

Thanks.

Re: only(Year)-1 doesn't work

Thu, 19 May 2011 08:48:11 GMT

Hi,

see the attached example.

Good luck!

Rainer

Re: only(Year)-1 doesn't work

IAMDV — Thu, 19 May 2011 08:48:56 GMT

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

Re: only(Year)-1 doesn't work

Anonymous — Thu, 19 May 2011 08:52:13 GMT

Thank you! It worked!

//A.

Re: only(Year)-1 doesn't work

IAMDV — Thu, 19 May 2011 08:54:26 GMT

Qlikview is interpreting the previous Field Names as string. And using the Only or Num function will not convert the Field type. However, if you use Num# - QV evaluates the expression numerically according to the string given as format-code.

So you if you use Num#(MyYear) -1 AS PreviousYear in the resident load, it should work. Do let me know if you need further help!

Cheers - DV