topic Re: Re: Re: Display data in a chart from dates sorted by weekdays in QlikView

Display data in a chart from dates sorted by weekdays

Mon, 17 Feb 2014 15:52:52 GMT

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

Re: Display data in a chart from dates sorted by weekdays

Colin-Albert — Mon, 17 Feb 2014 15:58:46 GMT

Can you post a sample app or your load script this will help put the answer in context.

You can calculate the day of the week using weekday(YourDateField>)

Re: Re: Display data in a chart from dates sorted by weekdays

Mon, 17 Feb 2014 16:31:27 GMT

This is my load script:

SET ThousandSep="'";
SET DecimalSep='.';
SET MoneyThousandSep="'";
SET MoneyDecimalSep='.';
SET MoneyFormat='Fr. #''##0.00;Fr.-#''##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

Directory;
CrossTable(Date, DataA)
LOAD F1,
    [02.01.2013],
    [03.01.2013],
    [04.01.2013],
    [07.01.2013],
    [08.01.2013],
    [09.01.2013],
    [10.01.2013],
    [11.01.2013],
    [14.01.2013],
    [15.01.2013],
    [16.01.2013],
    [17.01.2013],
    [18.01.2013],
    [21.01.2013],
    [22.01.2013],
    [23.01.2013],
    [24.01.2013],
    [25.01.2013],
    [28.01.2013],
    [29.01.2013],
    [30.01.2013],
    [31.01.2013]
FROM
(biff, embedded labels, table is Anrufe$, filters(
Remove(Row, Pos(Top, 53)),
Remove(Row, Pos(Top, 52)),
Remove(Row, Pos(Top, 51)),
Remove(Row, Pos(Top, 7)),
Remove(Row, Pos(Top, 6)),
Remove(Row, Pos(Top, 5)),
Remove(Row, Pos(Top, 4)),
Remove(Row, Pos(Top, 3)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1)),
Remove(Col, Pos(Top, 25)),
Remove(Col, Pos(Top, 1))
));

Directory;
CrossTable(Date, DataA)
LOAD F1,
    [01.02.2013],
    [04.02.2013],
    [05.02.2013],
    [06.02.2013],
    [07.02.2013],
    [08.02.2013],
    [11.02.2013],
    [12.02.2013],
    [13.02.2013],
    [14.02.2013],
    [15.02.2013],
    [18.02.2013],
    [19.02.2013],
    [20.02.2013],
    [21.02.2013],
    [22.02.2013],
    [25.02.2013],
    [26.02.2013],
    [27.02.2013],
    [28.02.2013]
FROM
(biff, embedded labels, table is Anrufe$, filters(
Remove(Row, Pos(Top, 7)),
Remove(Row, Pos(Top, 6)),
Remove(Row, Pos(Top, 5)),
Remove(Row, Pos(Top, 4)),
Remove(Row, Pos(Top, 3)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1)),
Remove(Col, Pos(Top, 1)),
Remove(Row, Pos(Top, 45)),
Remove(Row, Pos(Top, 44)),
Remove(Col, Pos(Top, 22))
));

etc.

The excel file looks something like this:

F1 02.01.2014 03.01.2014 04.01.2014 etc.

07:00:00 5 7 6

07:30:00 10 6 5

etc.

When I use the crosstable its like this:

F1 Date DataA

07:00:00 02.01.2014 5

07:30:00 02.01.2014 7

07:00:00 03.01.2014 6

07:30:00 03.01.2014 6

etc.

Using weekday(Date) as a calculated dimension hasn't worked for me.

My normal chart is set up with

Used Dimensions: F1

Expressions: Sum(DataA)

Thanks for your reply,

Best Regards,

Lucas

Re: Re: Display data in a chart from dates sorted by weekdays

Colin-Albert — Mon, 17 Feb 2014 17:05:51 GMT

The reason that weekday(DateA) does not work is that you need to convert your data to a recognised date field in QlikView.

Try adding this after the load script, where <YourTableName> is the table name created in your current load script. This will create a correct date field

Table2:

load

weekday(date) as weekday,

* ;

load

F1 as time,

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

DataA

resident <YourTableName> ;

drop table <YourTableName> ;

Re: Display data in a chart from dates sorted by weekdays

Tue, 18 Feb 2014 06:56:18 GMT

Thanks for the reply,

When I add your solution after the load script and name all my tables 'Main', it doesn't give me an error, but the chart doesn't work anymore (only naming one table 'Main' didn't work). It shows "No data to display" because of the drop table function, and if I remove it from the script, the chart shows the exact same as before. Is there any other way to format a Crosstable Field as a date?

Best Regards,

Lucas

EDIT: I think I can get the Crosstable Field to be recognized as a date, but now I need help with my chart. My dimensions are F1 and weekday(Date) = 'Mo'. My expression is Sum(DataA). It seems like the chart ignores the weekday(Date) = 'Mo' dimension.

Re: Display data in a chart from dates sorted by weekdays

Tue, 18 Feb 2014 07:14:30 GMT

hi Lucas

Please find Attachment

you will get weekly analysis

Re: Display data in a chart from dates sorted by weekdays

Tue, 18 Feb 2014 07:28:19 GMT

Thanks for the reply,

I have a Qlikview Personal Edition, so I can't view your document. Could you post a few screenshots or fotos instead?

Many thanks,

Lucas

Re: Re: Display data in a chart from dates sorted by weekdays

Tue, 18 Feb 2014 07:34:55 GMT

in dimension use this

if(Num(WeekDay(Date_field))=0 ,'Mon',

if(Num(WeekDay(Date_field))=1 ,'Tue',

if(Num(WeekDay(Date_field))=2 ,'Wen',

if(Num(WeekDay(Date_field))=3 ,'Thr',

if(Num(WeekDay(Date_field))=4 ,'Fri',

if(Num(WeekDay(Date_field))=5 ,'Sat',

if(Num(WeekDay(Date_field))=6 ,'Sun')))))))

please have look at image

Re: Re: Re: Display data in a chart from dates sorted by weekdays

Tue, 18 Feb 2014 07:50:43 GMT

Thanks for the reply!

It makes a lot of sense, and my chart should definitely work now, but it doesn't.

Here is my load script, maybe there's something wrong there:

SET ThousandSep="'";
SET DecimalSep='.';
SET MoneyThousandSep="'";
SET MoneyDecimalSep='.';
SET MoneyFormat='Fr. #''##0.00;Fr.-#''##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

Directory;
Temp:
CrossTable(Date, Data)
LOAD F1,
    [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$, filters(
Remove(Row, Pos(Top, 7)),
Remove(Row, Pos(Top, 6)),

Remove(Row, Pos(Top, 5)),
Remove(Row, Pos(Top, 4)),
Remove(Row, Pos(Top, 3)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1)),
Remove(Col, Pos(Top, 1)),
Remove(Col, Pos(Top, 24)),
Remove(Row, Pos(Top, 45)),
Remove(Row, Pos(Top, 44))
));

Temp2:
LOAD *,
Date(Date,'DD.MM.YYYY') as "Real Date"
Resident Temp;
DROP TABLE Temp;

My chart is below (it just shows the Sum of all data), and my expression is: Sum(Data)

Re: Display data in a chart from dates sorted by weekdays

Tue, 18 Feb 2014 07:52:14 GMT

can u attach qvw file i will do it

Re: Re: Display data in a chart from dates sorted by weekdays

Tue, 18 Feb 2014 07:57:57 GMT

Here is the file.

Re: Display data in a chart from dates sorted by weekdays

Tue, 18 Feb 2014 08:03:34 GMT

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

Re: Re: Display data in a chart from dates sorted by weekdays

Tue, 18 Feb 2014 08:06:36 GMT

Didn't work 😕

The chart is still the same

Re: Re: Display data in a chart from dates sorted by weekdays

Tue, 18 Feb 2014 08:12:51 GMT

send me you data sheet so i can reload application

Re: Re: Display data in a chart from dates sorted by weekdays

Tue, 18 Feb 2014 08:17:58 GMT

It's in my comment above yours

Re: Re: Display data in a chart from dates sorted by weekdays

Tue, 18 Feb 2014 08:32:38 GMT

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

Re: Display data in a chart from dates sorted by weekdays

Colin-Albert — Tue, 18 Feb 2014 08:48:25 GMT

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

Re: Display data in a chart from dates sorted by weekdays

Tue, 18 Feb 2014 09:01:10 GMT

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

Re: Display data in a chart from dates sorted by weekdays

Tue, 18 Feb 2014 09:29:05 GMT

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

Re: Display data in a chart from dates sorted by weekdays

Tue, 18 Feb 2014 09:42:19 GMT

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;