Posts by wonka1234

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

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


    Quote from Luke M;770220

    You could either create a new entry to "fill" that gap, maybe instead of having a city name put "down time" as a placeholder?
    Or, switch back to just looking at durations.


    Down time worked great, got any ideas on how to automate this in my data? manually putting this in where there are gaps can be tedious. Any formulas?
    I want to make it so whenever their is gaps I can have a row inserted for and get the down time.

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

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


    Thanks alot, seems like im finally figuring it all out, thanks to you. :D


    Having problems thinking of ways to display data that has points ie: A start at 5 end at 6 and B start at 530 and end at 7, which is kind of hard for me to do, not sure. any ideas?


    I am easily able to do a nice stack if data is like A start at 5 end at 6, B start at 6 end at 7, C start at 7 end at 8, which is sequential. This is simple because its sum . A + B + C , shows each stage and finished time. Whereas non sequential is not that simple.

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

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


    2. Hi Luke, Im thinking of just using Usain bolt data, as it is sequential and they do seem to go back to back. The stages that dont go back to back i'll destroy(which i think is only 'Ozzy'), would this help with the timings or still not mean anything?


    2.1 , if I wanted to start the Usain bolt times 4 hours later, I would just create a column with 4:00 hours and all the way down in the duration formula sheet? Then maybe make it invisible? This is an idea because I think louis starts before usain bolt (around 3-4 hours earlier), what is your opinion on this?

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

    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 :)

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

    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.

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

    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.

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

    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?

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

    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)

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

    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.

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

    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.

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

    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.