The thing is that the listbox of the date field already shows the numbers as displayed in Excel (40000 etc.) but I can change it in to whatever I want (timestamp, date) it doesn't change.
When I add a colum in my Excel and add some dates it works. So it is something with the crosstable and not recognizing the dates moved.
Crosstable creates a text field from the column names. So while the values look like numbers, they are text. You can use the num# function to convert them to numbers.
CrossTable(Date, Bedrag, 6)
Code, [Business Unit], Groep, KPI, [KPI Naam], Scenario, Bedrag,
num#(Date) as Date
drop table TempData;
I managed to get the date field to work.
Only problem now is that the field "Bedrag" (Amount) is not recognized anymore and is also transferred into date numbers instead of the euro amount it contained.
Executive Dashboard Demo.qvw 337.5 K