Why is Qlik Reading a number as a date? How can I change that?

[malradi88]

Dear Qlik Community,

I hope this finds you well. After crosstabling the script below the date field works but the 'scoremath' field produces a mixture of 'dates' and numbers. I checked the data source and all numbers in 'scoremath' are numbers less than 1 (0.2,0.6 etc...). There are no dates, but Qlik reads it that way (e.x. 12/30/1899). Is there anything I can do in the script to make Qlik treat the values as numbers? I tried num(scoremath) but that did not work either. Thank you for your support

[weekly quizzes pre school math]:

Crosstable ([Date], [scoremath],4)

LOAD

[الرقم التسلسلي] AS [preschool.الرقم التسلسلي],

[الاسم] AS [preschool.الاسم],

'weekly quizes prescohol' as flagpreschool,

حساب as [preschool-رياضيات],

    "43027",

    "43034",

    "43041",

    "43048",

    "43055",

    "43062",

    "43080",

    "43083",

    "43088",

    "43097"

FROM [lib://MM (trial-qlik-01_qliksupport)/Pre-school Assessments (5).xlsx]

(ooxml, embedded labels, table is [weekly quizzes]);

[Date weekly quiz]

Load

[preschool.الرقم التسلسلي],

'Math' as subjectflag,

'preschool' & '+' & [preschool.الاسم] as Key,

[preschool-رياضيات],

Date#(Date) as finaldate,

scoremath

Resident [weekly quizzes pre school math];

Drop table [weekly quizzes pre school math];

Best,

Mohammed

[duncanblaine]

Hi Mohammed

If you change

     Date#(Date) as finaldate,

to

     Date(Num#(Date#(Date))) as finaldate,

Seems unnecessary but it works! I tested it locally.

[duncanblaine]

Sorry, just seen that Date wasn't the field you were having trouble with.
If you could post some test data it would help diagnose.

[malradi88]

Hi Duncan,

Thank you for your responses Please find test data attached.

Best,

Mohammed !

[duncanblaine · Accepted solution]

Have you tried Override Document Settings for scoremath in the Number tab in your Chart properties?

[malradi88]

Hi Duncan,

I cannot seem to locate the the number tab that allows me to override document settings. I have changed the data format from 'general' to 'number' though and still the same result.

Best,

Mohammed

[sagar_puri]

Hi Mohammed,

Try adding few parameters like num(scoremath,'#,##0.##', '.' , ',')

Regards,

Sagar.

[malradi88]

Nevermind my mistake it worked!

[duncanblaine]

I found that the following worked:

Num(Num#(scoremath,'#.#')) as scoremath

There are quite a few other characters amongst the scores, which might be confusing Qlik.

[malradi88]

Thank you Sagar that is a useful expresison to have