So I have an excel table, with dates from 2017 on. I have two other columns showing sales and forecast,until september 2022.
I upload the excel table in data manager, then I create the bar chart, and the dates are not working correctly.
I have everything alright until september 2022, from september 2022 I have "42005","42036" etc etc- I want to get rid of that. (and no bars, because there were no sales)
Hi, usually, when loading date fields from excel to Qlik, they are treated as numbers and convert them to date format you need to use function 'date'(). For. example:
=date('42005') is actual 2015-01-01, =date('42036') is 2015-02-01 etc.
So, first, please check does your excel tables really are from 2017 as you think. Second, when loading your excels, you can define the period with WHERE clause and get rid of these "42005","42036". It depends on your load script, but structure of it could look like this:
LOAD your_date_field, A, B, C //... FROM [lib://AttachedFiles/your_excel_1.xlsx] (ooxml, embedded labels, table is Sheet1) WHERE date(your_date_field)<'2022-10-01' and date(your_date_field)>='2017-01-01';
