topic Re: Weekly Cycle in QlikView

Weekly Cycle

stevegimbrollmt — Wed, 06 Nov 2013 10:45:27 GMT

Hi all

Not sure if there is any interesting method in achieving something like this:

	16/09/2013	23/09/2013	30/09/2013	07/10/2013	14/10/2013	21/10/2013	28/10/2013
Call Answered	1234	2342	2342	234	23423	15127	16357
Calls Rejected	4324	2342	2432	23423	234	19269	21508
Emails	423423	2342	242	2342	2342	5713	3773
Web Views	4234	2432	2342	2342	234234	130507	138090

Where I would have a daily record but I need to represent the figures on weekly cycle starting on Mondays for the past 7 weeks.

Any ideas? Do I need to use the Ceil function?

Thanks

Steve

Re: Weekly Cycle

israrkhan — Wed, 06 Nov 2013 11:00:15 GMT

create a week field in load script,

like below

load * ,

week(Date) as Week Num

from .......

and then take pivot table, add [Call Answered Field] to your first dimension and week-number to your second dimension,

and then select and drag up week number dimension to horizontal.

Re: Weekly Cycle

Gysbert_Wassenaar — Wed, 06 Nov 2013 11:00:35 GMT

You want to show only the values of the mondays? In that case you could create a flag field in the script for the mondays:

Load x,y,z,

MyDate,

if(weekday(MyDate)=1,1,0) as IsMonday

from ...

You can then adapt your expression using the new IsMonday field. Count(Value) would become count({<IsMonday={1}>}Value).

If you want to show weekly totals, but show the dates of the mondays you create a field StartOfWeek: weekstart(MyDate) as StartOfWeek. You can then use this field as dimension instead of MyDate.

Re: Weekly Cycle

stevegimbrollmt — Wed, 06 Nov 2013 11:04:51 GMT

That would be good for Mondays but as you asked, the values I need them still to be weekly but sort of the date start of the week would be the Monday as I shown in the sample above.

Thanks a lot for your feedback

Steve

Re: Weekly Cycle

stevegimbrollmt — Wed, 06 Nov 2013 11:10:12 GMT

I have used =WeekStart(Date) in the Calculated Dimension and it seems I got what I needed.

All I need to do now is do it for only the last 7 weeks.

Thanks..

Steve

Re: Weekly Cycle

Gysbert_Wassenaar — Wed, 06 Nov 2013 11:17:52 GMT

You can limited that in the expression. Something like this:

count({<Date={'>$(=max(Date)-49)<=$(=max(Date))'}>}Value)

Perhaps you'll want to use a weekstart in there too if your max Date isn't a monday.

Re: Weekly Cycle

stevegimbrollmt — Thu, 07 Nov 2013 09:47:42 GMT

I did something simliar but I just created a Week field and worked on that.

Basically did thse 2 steps:

=WeekStart(Date) in the Calc Dimension
Sum({<WeekField={'<=$(=max(WeekField))>$(=max(WeekField)-6')>}Value) in the expression

Thanks again for your help