Pivot, Summarize values by Maximum, 24-hour clock

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    A different chart for each stage? You could do that. Just need to copy the PivotTable and chart several times, but you could link your slicer to all the PTs.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    Good idea, what about custom horizontal lines that I can define on any pivot chart?

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    You can't add custom series/lines to a PivotChart. If you want to do that, you'll need to build a regular chart and data mine the PT.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    Can't a column be added to the data with a constant time (i.e. 4 Am) and make that a line in the Pivot chart?


    Sigh maybe ill go to regular charts, but if that data is constantly being added daily, that may be hard to upkeep?

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    Sorry, you could do that. I misunderstood what you meant by a custom line.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    Okay last question and ill leave you alone,


    Would you be able to make a bar chart(not from pivot) similar to the pivot , of usain (actual and expected;expected being a line again), with stages(or max of each individual stage stacked cluster, ie max of new york, phillidelphia, wash and LA), with the "random line" aswell?
    Then I can mimick what you did for the rest?

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    Hi Wonka,


    I still don't think you've thought about whether you want to display duration or finish points very well in how they'll be displayed, but here's one way to do what you asked. I went ahead and built the chart both ways, one showing max times of when they actually finish, the other showing durations so that the total stack equals max time overall. For the stages, I used 5 colors, with the expected stack using a slightly lighter shade of each color. Y-axis on each has been set to some time duration.


    The random line has been added to each as well. Please look over the different formula sheets and study how they are calculated. Also, compare the two different chart layouts, and really think about what you want to visualize, as well as how it should be visualized.

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    Thanks alot Luke. :) . For the actual chart, is it possible to make that real time , instead of 18:00 then repeat?


    Also what is tbData?

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    Not sure what you mean by real time...you mean like
    24:00
    30:00
    36:00


    Setup? If yes, change the format to be:
    [h]:mm


    with the square brackets.


    tbData is the name of the table I created. Since we're dealing with several arrays, don't want to have to work with whole column, but also want to make sure we're always grabbing all the data. Using tables and structural references is the best way to do that.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    well for 'Actual Chart' I would like to have it the same format as before, 8 pm to 12 am the next day. And make my 4:00 am line at 4 am, so its comparable.

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    Sure thing. Just change your min/max values as appropriate. We've talked about that before. :) In the sample file, I didn't bother setting the min/max, only changed the major unit to be every 6 hours. Adjust as desired.
    The 4 am line is at 4 am...it's just how the spacing on y-axis is marked.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    I tried the


    Min .8333
    Max 1.5
    Major unit .04166666


    Timing is great but bars are not fully visible, and lines are not seen :(

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    Correct...if you reduce the max to be less than your biggest value, you won't see the whole bar. The problem with the "actual chart" is that each stage is a growing cumulative, so the overall height of the stack if several days. This is why I suggested that you were really wanting duration.


    If you want the gridlines, select the chart, then go to Layout - Gridlines, and select the option you want.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    Duration is what I want lol. Sorry im an idiot.
    Is there a way of doing the duration from like 8:00 pm night before till 12 next day? I have trouble using the above numbers to start the day prior (10 pm).

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    You could change the y-axis to limit it to just that view...not all of the data will be fully visible. I'd suggest starting with just setting the Max, and then see where the value is lying. You certainly wouldn't see the red line since it's set to be earlier.


    Remember though, when dealing with duration, don't think of it as 8 pm...it's just the first 20 hours. If you set the min to "8 pm", then you're really saying you don't care about whatever person X did for the first 20 hours, which I'm not sure is accurate.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    If I want to unlink these graphs? And put them each on separate pages? how do I do that?


    Also you mentioned you named the table tbdata? where do I alter that?

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    Select a cell in the Table, and then go to Design ribbon, top left.


    Not sure what you mean by "unlink". You can cut/paste them wherever you want.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    Hi Luke, how did you create the multiple charts and filter option? Without doing a pivot chart?

  • Re: Pivot, Summarize values by Maximum, 24-hour clock


    The filter is a Data Validation dropdown. If you look at the formula sheet(s), you'll see that it takes that input and plugs it into the formulas. The array calculations then do the rest.
    As for having the two separate sheets (each one has one chart), after I built the first formula+chart sheet, I just copied the sheets, and changed the formulas slightly to look at duration rather than time of day.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!