Defult values for weeks in Listbox 2

[EliseFreedman]

Hi There

I am developing a KPI Dashboard for work.

I am trying to make it versatile so that individual users can customise it to suit their own requirements.

I have added a multiselect listbox with our weeknumbers 1-52. I want the default values to be the current week of the year plus the previous 11 weeks i.e 12 weeks in all.

If the current week is within the first 12 weeks of the year, then the listbox should default to weeks 1-12. Once we get into week 13, the listbox should default to weeks 2-13. Obviously I could just write one big bit of vba i.e If currentweek >0 and < 13, then show 01-12, else if currentweek=13 then show weeks 2-13. This seems awful cumbersome so I was wondering if there was a better way

 

[Andrzejek]

Here are some VBA logics for Week Numbers based on current date. This looks promising to start with:

Option Explicit

Private Sub Command1_Click()
Dim intWeekNo As Integer
Dim intW As Integer

intWeekNo = WeekNumberAbsolute(Date)

With ListBox1
    .Clear
    Select Case intWeekNo
        Case Is < 13
            For intW = 1 To 12
                .AddItem intW
            Next intW
        Case Is > 39
            For intW = 40 To 52
                .AddItem intW
            Next intW
        Case Else
            For intW = intWeekNo - 12 To intWeekNo
                .AddItem intW
            Next intW
    End Select
End With

End Sub

Public Function WeekNumberAbsolute(DT As Date) As Long[green]
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberAbsolute
' This returns the week number of the date in DT based on Week 1 starting
' on January 1 of the year of DT, regardless of what day of week that
' might be.
' Formula equivalent:
'       =TRUNC(((DT-DATE(YEAR(DT),1,0))+6)/7)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/green]
    WeekNumberAbsolute = Int(((DT - DateSerial(Year(DT), 1, 0)) + 6) / 7)
End Function

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

@Andy, do you really need Case > 39?

The Case Else works for every week >= 13, Current Week plus the previous 11 weeks. Two Cases total.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

Then Elsie, have a question for you. Do you want weeks 1-12 for any week less than 12, when that week’s data hasn’t arrived yet.

Or would you get..
[pre]
Week. Selections
1. 1
2. 1-2
...
11. 1-11
12. 1-12
13. 2-13
...
40. 29-40
41. 30-41
...
52. 41-52
[/pre]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[EliseFreedman]

I would be happy to just have the weeks with the data. However when I try to do charts with fewer weeks, the bars become thick rather than staying at a consistent size irrespective of how many weeks. Is there a way around that?

 

[SkipVought]

I’d modify Andy’s code thusly...

‘
intWeekNo = WeekNumberAbsolute(Date)

With ListBox1
    .Clear
    If intWeekNo - 11 < 1 Then
       intWeek = 1
    Else
       intWeek = intWeekNo - 11
    End If

    For intW = intWeek To intWeekNo
        .AddItem intW
    Next intW
End With

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[EliseFreedman]

How do I keep the width of the bars consistent even if I have fewer weeks

 

[SkipVought]

Are you referring to a Bar Chart, like a Gantt Chart?

What table/values do you have for charts?

Plz give examples of less than 12 weeks and 12 weeks

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

Can you post a workbook example?

Why wouldn’t you have 12 weeks for weeks 1-11 using data/weeks for the previous year? Should tha data not be KPI relevant? Why would that data be ignored? It’s ongoing relevant info, it seems to me.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[EliseFreedman]

Yes, I am using barcharts.

The values are all to do with closure on time/late of actions arising from various different activities. I have got the values in a single sheet that I am using to populate my dashboard. Whilst I want the default to be 12 weeks, I would like the user to have the option of a different time period such as 4 weeks, 8 weeks etc. I can do that at the moment but the bars become very wide. I would prefer them to stay the same thickness regardless of the time period selected

 

[SkipVought]

Plz see the previous post. An example workbook with chart would be helpful.

Shooting from the hip, if 12 weeks will be the max, then weeks less than 12 might be set to ZERO (no data).

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[EliseFreedman]

It’s likely thatgoing forward we will always have 12 weeks but we made the decision that as it’s a new system, we would start afresh from 1st January

 

[EliseFreedman]

I will attach a file in the morning when I get to work

 

[SkipVought]

Thx.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[EliseFreedman]

Hi

I have attached a sample version of the file

If you tick the checkboxes for weeks 1-12 then click update dashboard then repeat for just weeks 1-4, you will see what I mean when I say that the bars change size/thickness. Ideally, they would stay the same thickness regardless of how many weeks are selected

 

[Andrzejek]

@Andy, do you really need Case > 39?

Yes, you are right.
I don't know what I was thinking,...

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

IMNSHO using arbitrary start and end periods to measure effort (like month end, quarter end, year end) encourages behaviors that result in HOCKEY STICK efforts rather than ongoing steady efforts. That’s just my 2 pence worth.

I’ve uploaded your workbook having modified the Actions Not Yet Closed chart (chart 2) to only link to 3 weeks of data, rather than 12 AND having reduced the Plot Area WIDTH so that the chart column widths are closer to those in the first chart of 12 weeks (chart 1).

My next step would be to
1) overlay chart 1 with chart 2 (of course, you’ll need to make the Chart 2 Chart Area and Plot Area have NO FILL.
2) record the width of both Plot Area WIDTHS.
3) calculate the chart 2 Plot Area WIDTHS for 1 to 11 by extrapolation and put those values in a table for use for weeks 1-11.
4) calculating the source data range to comport with the current week and the selected week range.

Thats an extra pence worth.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[EliseFreedman]

I cant see anywhere to view/modify the plot area widths

 

[SkipVought]

You can do it manually by selecting any chart object, like Plot Area, when you select any Chart on your sheet. Chart Tools > Format...and select Plot Area. Then drag the Plot Area width left or right.

But you’ll eventually need to do this via VBA.

On your sheet you have a ChartObject object that has a Chart object and the Chart object has a ChartArea and a PlotArea, for instance...

With ActiveSheet.ChartObjects(1).Chart
   With .PlotArea
      .Width = 50
   End With
End With

I think I got that right. I’m not at my laptop at this time.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein