1 2 Previous Next 23 Replies Latest reply on Oct 8, 2018 6:39 PM by swaroop.gantela

    Time Calc

    Steve Pitman

      I received help earlier but it didn't help when my State ( UP/DN ) changed multiple times.

      Im looking to calculate UP/DN time up to today for each Thing ( A/B/C/Etc..)

      Some Never go up and some never go down.

      The chart shows Things reporting in a file.

      Date across the bottom (data is union-ed and added by a person who may or may not add each day as you can see from the sporaticness of the marks )

      Things show up in the file(row) as UP then after a period of time will show DN and I'm looking to calculate the amount of time this thing has been up and DN up to today assuming the last state of the item.



      Thanks for the help!

      Im stumped

        • 1. Re: Time Calc



          It's me again.

          Happy to give it another try.


          I think the previously described method should generally work

          on any granularity of time. It had used days because that was

          the setting in my fake data, but it should work the same on a minute

          or second level.


          It also shouldn't be dependent on how much time elapses between

          points as it only looks to see what the previous state and time were.

          Similarly it just compares between two points, so shouldn't matter

          how many or how rapidly the ups and downs come.


          Would be grateful if you could post some fake data that is representative

          of the time scale that your data comes in.

          • 2. Re: Time Calc
            Steve Pitman

            Wilco, Stanby. ill generate some data

            Thanks for the help Swaroop!

            • 3. Re: Time Calc
              Steve Pitman



              Here's a better view in to the Data.

              Its a Mess

              • 4. Re: Time Calc



                I implemented the scheme on your new data and it seemed to

                give reasonable results, but that will need to be validated.

                I made an attempt at converting the Ups and Downs into bars

                (please see screenshot below), but that too will need confirmation

                that it's doing what it should.


                I also made the settings for the Table Calculations as defaults.

                Please see screenshot below.


                Please see the workbook v10.3 attached in the Forum Thread.





                • 5. Re: Time Calc
                  Steve Pitman

                  This is looking great. How can I Alias the Nulls from them Being 0% Avail? Im gonna spend some time and verify ill get back with you.


                  Looking great though. I really Appreciate the help.

                  • 6. Re: Time Calc
                    Steve Pitman

                    What about the gap just behind Today() or the last record closest to today

                    • 7. Re: Time Calc



                      Below is the latest version.

                      Needed to clean up a few things.


                      1. The Nulls can be set to zero using the ZN function:


                      So [Availability] would become:

                      WINDOW_SUM ( ZN ( [DaysUp] ) ) / WINDOW_SUM ( [DaysInState] )


                      2. As you found, [Days in State] wasn't correctly calculating the days for the very last day.

                      It needs to calculate both the time back to the previous mark, as well as the time forward to TODAY().

                      So the calculation is now:

                      IF First()=0 THEN 0 // if first day, just give 1 0 days

                      ELSEIF LAST()=0 THEN DATEDIFF('day',ATTR([Date]),TODAY())  // if last day, days until today

                                          + DATEDIFF('day', LOOKUP(ATTR([Date]),-1),ATTR([Date])) // plus time to previous mark

                      ELSE DATEDIFF('day', LOOKUP(ATTR([Date]),-1),ATTR([Date])) // days between previous day and this day



                      3. Because TODAY() is not actually in the dataset, a hack was needed to get the part from the

                      last date to TODAY() to plot. The bar length as calculated above should be correct, it's just

                      we need to shift the starting point.

                      So the (Date for Plotting)

                      IF { FIXED [Thing]:MAX([Date])}=[Date] // date is equal to max date for a Thing

                      THEN TODAY() //shift the bar starting point to Today

                      ELSE [Date] // otherwise just use the date as is



                      Workbook v10.3 attached in Forum Thread.



                      • 8. Re: Time Calc
                        Steve Pitman

                        Let me plug this in see how it goes.

                        Can you explain ZN?


                        I cant thank you enough for the help!

                        • 9. Re: Time Calc



                          From the Knowledge Base:

                          "ZN() returns the expression if it is not null, otherwise returns zero.

                          Use this function to use zero values instead of null values."

                          • 10. Re: Time Calc
                            Steve Pitman

                            Thanks swaroop.gantela I'm verifying the data now. takes a while. Thanks for the help. Looking good so far.


                            one thing i noticed is the bars seem to overlap in certain scenarios. and the last bar makes a large one that overlaps and sometimes makes a huge bar.

                            I've included a picture with  the bar length and one with it removed.

                            I colored by bar length to highlight the large bar that shows up at the last record for that ID.

                            • 11. Re: Time Calc



                              In the test dataset that I'm using, the standard settings did not create

                              any overlap (see top two graphs of Dashboard 1 of the attached).


                              But I was able to create the big bars and overlap if in the table calculation settings

                              I switched the positions of [State] at [Date for Plotting] (see "overlap" of the attached).


                              Please see if it the Table settings match that shown below.

                              If they already match, then would be grateful if you could

                              post some screenshots of your entire screen including the pills.



                              • 12. Re: Time Calc
                                Steve Pitman

                                Here is One ID I put the Date pill on the Rows Discrete it shows the latest bar being long.

                                That specific Row is only one ID Showing UP.

                                Bar length.PNG

                                • 13. Re: Time Calc



                                  I think I see what may be happening.

                                  My apologies, I did not make it clear, but I am using a separate calculated field

                                  for the date called [Date for Plotting]:

                                  IF { FIXED [Thing]:MAX([Date])}=[Date] // date is equal to max date for a Thing

                                  THEN TODAY() // shift the bar starting point to Today

                                  ELSE [Date] // otherwise just use the date as is



                                  When I use that, the blocks come out correct,

                                  but using just the simple date, there is the long bar.

                                  Please see below.





                                  • 14. Re: Time Calc

                                    Here is the workbook for the above

                                    1 2 Previous Next