Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Have you tried Override Document Settings for scoremath in the Number tab in your Chart properties?
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.
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.
Hi Duncan,
Thank you for your responses Please find test data attached.
Best,
Mohammed !
Have you tried Override Document Settings for scoremath in the Number tab in your Chart properties?
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
Hi Mohammed,
Try adding few parameters like num(scoremath,'#,##0.##', '.' , ',')
Regards,
Sagar.
Nevermind my mistake it worked!
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.
Thank you Sagar that is a useful expresison to have