Pivot, Summarize values by Maximum, 24-hour clock

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

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


    Is their a way to show the actual chart you did in a primary or secondary axis 8 pm to 10 am form. Example usain bolt feb 29, 9:11 am, say I have a random line of 8 am, usian bolt bar would go past this the expected line ?


    Duration chart does not make sense to use right now as feb 29 is supposed to be longer as it finishes at 9 am, which is one of the later finish times.

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


    Lucas, would you be able to in actual chart:


    1) Change the times in the actual chart axis or use a secondary axis if need be from 8 pm previous day, to 10 am this day
    2) insert a few expected lines, so ex: one at 8 on the next day, to compare with all the finish times. The idea is to compare what finish times passed the line and which were under the line.

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


    You can change the y-axis min/max as you want to not show data as you wish. There is no "previous" day in XL, as time starts at 0. I think you mean today and the next day. Ten am of the next day would be 34/24.
    To add some lines, you can use the example I did with the dummy line at 8 am. Again, just pay attention to which day you're working with.

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

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


    Would you show me luke? I am confused. In your actual chart the hours go to 18:00 then repeats. I want a timeline, 20, 21,22,23,24,1,2, etc. And the times matching, with the random lines. The line is not so much the problem, but the formatting and getting the lines to correspond with the data.


    I can do 34/24 for miniumum on the axis but it 1) repeats. 2)hides part of my data stacks and/or lines. I need the actual times to reflect accurately with the axis.

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


    To change the spacing of the items on the axis, you need to change the Major Unit. First, reviewing our calculations
    [TABLE="width: 263"]

    [tr]


    [td]

    8 pm of current day equals

    [/td]


    [TD="align: right"]0.833333[/TD]

    [/tr]


    [tr]


    [td]

    10 am of next day equals

    [/td]


    [TD="align: right"]1.416667[/TD]

    [/tr]


    [tr]


    [td]

    One hour increment equals

    [/td]


    [TD="align: right"]0.041667[/TD]

    [/tr]


    [/TABLE]


    Plug those 3 numbers in for the min, max, and Major unit. That is what you what you asked for how chart axis should work. Now, keep in mind that by doing that, you won't show the part of the bar that goes from 0 - 8 pm, or the part that is past 10 am of next day. I took one of your earlier charts and used it in this example.

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


    You can change axis of any chart. I just happened to use the Pivot one in my example.

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

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


    When I change the axis of the 'actual chart' I cannot get the random line to show 8 am. on this same scale you just suggested. It ends up shifting everything.

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


    Make sure that you're using a value of 32/24 for your 8 am line, or it won't show up in the chart's range of 20/24 to 34/24.

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

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


    No, that value of the line is whatever you made the value in a cell(s). It's not a chart setting.

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

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


    Hi Luke, could you explain to me (from the stacked columns example_


    1)how you made the data a table and name it?
    2) how you made the charts link and be accessible by the drop down? how did you make each chart have its own data on the same sheet? (ex usain data and LukeOZ data are on the same sheet but when u change chart, the data changes)

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


    Hi Luke,


    You know how you calculate the MAX for each stage, how would one get the TOTAL of each stage, I tried SUM, didnt work.

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


    =IF($A5,"",IF($B5="","",MAX(IF((tbData[Business Date]=$B5)*(tbData[Stages]=E$4)*(tbData[Stream]=$B$1),tbData[Actual Duration]))))


    I feel like the duration chart is not accurate, as the 29th data should be larger, So would a SUM formula be better used? instead of Max?

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


    Hi Wonka,


    1. To make a table, you go to Insert - Table (if data already exists, select all your data first). On the Design ribbon, top left, you can change the name of the table.
    2. There are two different formula sheets, each driving a chart. Both formula sheets look at same data. The formula sheets have a linked cell in top left that links to the dropdown on each respective chart sheet.
    3. Depends on what you mean by total? When you are grabbing the max, you're actually grabbing a point in time. If you want total time taken, we're no longer talking about dates in a calendar, but just duration. Total duration would be the sum of all the individual durations.
    4. I don't know which person you're looking at, so don't know how to answer. For the most part, all 4 people appear to have consistent data for Feb 29. Why do you think it would be different?


    Again, I don't think you've thought about what you really want to plot yet. You've got data that shows a series of start/stop points in time. What info do you want to know?

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

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


    As for #3, by total I mean accurate timing, because there are multiple stages for each stream it doesnt plot the end time accurately, so yes duration for each date. Because on the chart I want to show say 0 hours to 18:00 (or 4 PM on T to 10 am on T+1)


    [ATTACH=CONFIG]68819[/ATTACH]



    Here you can see Washington and LAmultiple times, the current formula takes into the Max of these stages, correct? How could I add them together to get the total time of the so I can see it on the stacj end at 9:11 on T+1, or duration total being 17:11 (if starting at 0 or 4 pm on T).


    Does this help clarify? I find that duration chart may help better display this.
    Overall the goal is to measure the times of if the runner finish on the expected finish time, and then be able to check each stage to see which stage they struggled on.

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


    In that case, we're back to talking about duration. The date portion of the time doesn't matter, only how long it took to get from Start to Finish. We can put the date on the chart, if you need to know what date the event was, but that's it.
    Easiest to calculate by using a PivotTable. We'll throw the durations into the data fields, and row field can be date and/or stage. See attached for example.

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


    Yes duration. The Problem how I would like to display is:


    This data 1) The overall duration, it seems like it is just certain stages showing on this pivot, which is good I just prefer a different view:( stacked columns of each stage showing the total of each stage.) So for usain, first LA total, then new york total, and so on, stacked ontop of each other instead of side by side.
    I want total duration, but showing the stages within that total duration, if that makes sense.

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


    Hmm, well, can't create a cluster-stack chart with a PT, so go back to formulas. I think I see what you meant earlier, we can change the formulas to be a SUM rather than MAX.
    Here's the duration chart again, using SUMs. Added some formatting. Random/goal line is not easily movable via spinner.

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


    Wow thanks alot.
    1) When I tried making sum on my chart before it wasn't working, I just replaced Max with Sum, no worries I can use your as a template. Just weird why. Did you change something else?


    2) Looks really good, is there a way to put a secondary axis and maybe show the times? So duration 0 would be 20:00 and so on, 12:00 on primary would be 8 am on secondary?


    3) What do you mean not easily movable? It works pretty good :)

Participate now!

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

Register Yourself Login