one week occurs in two months - how to handle this?

[josephinetedesc]

Hi

I am getting strange answers when calculating monthly and weekly values - see below

So for example when I choose the month of August, and the x axis is in weeks - I get a week num of week 31, with 100% activity

Thankfully this does not happen very often.  Is there a way of overcoming this problem?

Jo

DateOfInt	WeekNum	month
Sunday, 2 August 2015	32	8
Saturday, 1 August 2015	31	8
Friday, 31 July 2015	31	7
Thursday, 30 July 2015	31	7
Wednesday, 29 July 2015	31	7
Tuesday, 28 July 2015	31	7
Monday, 27 July 2015	31	7
Sunday, 26 July 2015	31	7
Saturday, 25 July 2015	30	7
Friday, 24 July 2015	30	7
		location
month	weeknum	East	Grand Total
7	27	9310	22665	41%
	28	15080	37040	41%
	29	15690	37885	41%
	30	15675	36590	43%
	31	14815	36250	41%
7 Total		70570	170430	41%
8	31	540	540	100%
	32	15500	37435	41%
	33	15060	38495	39%
	34	14915	36795	41%
	35	15575	38055	41%
	36	2975	8095	37%
8 Total		64565	159415	41%
Grand Total		135135	329845	41%

[jonathandienst]

How are you calculating the week numbers? How do you want to assign value for the split weeks?

[josephinetedesc]

Hi Jonathan

I use the fuction in QlikView

Weekname or WeekStart(dateofInt)

Jo

[ramoncova06]

weeks and months do not have a 1 to 1 relation so that is what causes this, do you have daily information ?

[swuehl]

Is the Grand Total column the total of several locations? I can't see from your Excel file how the East values and Grand Total values are calculated, so if possible, maybe you can explain your setting a bit more detailed.

If your biggest worry is about showing a 100% activity, I would check why the Grand Total equals East for week 31 in August.

In general, if you want to compare weeks, I would try to avoid splitting week 31 in parts (which makes it hard to compare to other weeks). You could assign a week to the month it starts or to the month more than 3 days fall into, for example.

[josephinetedesc]

Yes the figures come in hourly and then rounded up.

[MarcoWedel]

Hi,

another solution could be to define a set expression that disregards month selections but keeps all weeks that have at least one day within the selected month:

hope this helps

regards

Marco

[josephinetedesc]

The purpose of this QlikView document is to show Utilisation - so we have the machine activity - from a set date in the past (usually 3 months) to a set date in the future.  Future dates are only meaningful for about a month ahead as activity is rarely booked more than a month ahead.  When giving a figure for utilisation - the problems are:

1. If no activity occurs - then % utilisation is skewed - I have explained that in another thread.

2. The dashboard is able to "drill down".  from year, month, week.  This is where the current problem occurs.

Jo

[josephinetedesc]

Hi Marco if I use your way - using the set analysis - I would need to do some amazing scripting work?  For exmaple:  if month chosen, the weekly graph would not be shown, but if the weekly graph is chosen - then the month graph would not be chosen.

How amazing is the scripting work I would need to do?   It certainly seems the best way of solving the problem.

I will try and sketch what the document page looks like - and add it to the next reply.

Jo

[josephinetedesc]