Display data in a chart from dates sorted by weekdays

[#1]

Hi,

My chart is Time (x-axis) to Amount of Sales (y-axis). Time and Date are my key fields. All my data is loaded from an excel sheet as a crosstable.

I would like my chart to display the Amount of Sales from only Monday, or Tuesday, or Wednesday, etc.

I have tried a lot of things like the weekday() function, but nothing has worked for me.

Could you please help me?

Best Regards,

Lucas

[#2]

Here is the file.

[#3]

just change name Date to Dates in Script and in expression also

[#4]

Didn't work 😕

The chart is still the same

[#5]

send me you data sheet so i can reload application

[#6]

It's in my comment above yours

[#7]

Interesting... I can't change the Dates data by changing the format in the document settings...

e.g. if I change 'Dates' to 'Money' format, it will still show 02.01.2014 and not ####.##$

or if I change 'Dates' to 'Number', it will still show 02.01.2014 and not 41760.00

[Colin-Albert]

Hi Lucas,

You will need to use date# to convert the text value to a true date field

date(date#(Date, 'DD.MM.YYYY'))) as date,

This is a bit confusing as your date field is named "date", the same as the date function.

Have a look at this document on handling date fields in QlikView

http://community.qlik.com/docs/DOC-3102

[#9]

Hi Colin,

I have tried using only date#() or only using date() or both, and nothing has worked. Since the weekday() syntax is weekday(timestamp), I did the same with timestamp (timestamp#(), timestamp(), timestamp(timestamp#())), but that hasn't worked either.

Thanks for the document and the reply.

We might need Henric Cronström

[#10 · Accepted solution]

Turns out I found the answer!

I had to change the "date#(Dates,'DD.MM.YYYY')" to "date#(Dates,'DD.MM.YYYY') as DatesNew" and then replace the 'Dates' fields with 'DatesNew'!

Thanks for your answers anyway and have a great day,

Lucas

[#11]

hi just found answer In script do this

Temp:

CrossTable(Dates,Data)

LOAD F1 as Time,

     [02.01.2014],

     [03.01.2014],

     [06.01.2014],

     [07.01.2014],

     [08.01.2014],

     [09.01.2014],

     [10.01.2014],

     [13.01.2014],

     [14.01.2014],

     [15.01.2014],

     [16.01.2014],

     [17.01.2014],

     [20.01.2014],

     [21.01.2014],

     [22.01.2014],

     [23.01.2014],

     [24.01.2014],

     [27.01.2014],

     [28.01.2014],

     [29.01.2014],

     [30.01.2014],

     [31.01.2014]

FROM

x.xls

(biff, embedded labels, table is [Anrufe$]);

Temp1:

LOAD Date(Date#(Dates,'DD.MM.YYYY'),'DD/MM/YYYY')as Dates,

Sum(Data) as Count

Resident Temp

Group by Dates;