1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table calculated field

Discussion in 'Ask an Excel Question' started by Tauqeer, Jan 4, 2017.

  1. Tauqeer

    Tauqeer Member

    Messages:
    42
    Hi

    I am attaching this file, this workbook runs the SLA between the 02 timeframes and does the conditional formatting as per the macro, if you look the sheet SLA it has run the macro, I am using the 23rd dec data, also I have got 23rd graph sheet , which has the pivot table which capture the volumes from the SLA sheet, which updates the graph, I have to calculate the within SLA figures by doing filtering and its time consuming, is there any way I can add calculated field in pivot table or some other way of capturing the within SLA figures so it can update the graph dynamically.

    Green colour means its with SLA which is less than 1 hour
    and red colour means outside SLA which is over 1 hour.

    please advise.

    thanks

    Tauqeer

    Attached Files:

  2. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi

    Since all the info is already available, it is only a matter of identifying if it is within SLA or not... you already did that with colors but if you add another column to the table in sheet "SLA" with the following formula (see below) it will then allow you to use it in the pivot table by summing or counting the "1":

    =IF([@SLA]*24<=1;1;0)

    Hope this helps
  3. Tauqeer

    Tauqeer Member

    Messages:
    42
    Hi
    Thanks for your email, I tried the formula in the new column
    But it only bring zeros , could you please advise on this.
    Thanks
    Tauqeer
  4. p45cal

    p45cal Well-Known Member

    Messages:
    663
    See attached. Graph etc. is based on sheet month data, which has had 2 columns added for integer of date (date received at the moment) and a column showing In or Out depending on whether within the hour or not.
    Formulae updates in C4:E5.
    Change the date in cell B12, see update in cell C2, and chart etc.

    Attached Files:

    Thomas Kuriakose likes this.
  5. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi,

    Please see attached... it is probably only showing you "0" because you have it formatted as time like "00:00". If you switch to general it will show the "1"

    Attached Files:

    Thomas Kuriakose likes this.
  6. Tauqeer

    Tauqeer Member

    Messages:
    42
    HI
    Mate thanks a lot for your help, I build the formula within the SLA macro, the only thing I need to ask you how to change the colour back to no fill in the within SLA column as cell becomes green and red when the macro runs in the within SLA column, please advise.

    I am attaching the file...
    thanks

    Tauqeer

    Attached Files:

  7. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi,

    That happens because you are inserting the new column based on the "SLA" column which has condition formatting applied... to remove simply add to your code:
    Code (vb):

    Columns("G:G").FormatConditions.Delete
    With Columns("G:G").Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
     
    Another way would be to use
    Code (vb):
    CopyOrigin:=xlFormatFromRightOrBelow
    instead of
    Code (vb):
    CopyOrigin:=xlFormatFromLeftOrAbove
    which would copy the format of the column on the right instead of the left
    Thomas Kuriakose likes this.
  8. Tauqeer

    Tauqeer Member

    Messages:
    42
    That's great mate.. Thanks for your help,
    one last thing that is bothering me,
    if the sla sheet is empty and someone
    Accidentally press sla macro then it mucks
    Up everything, or when someone press sla
    Twice then it will rerun macro with extra
    Col, or when someone press clear twice on empty
    Sheet then it will delete the extra cols , is
    There anyway in the macro to stop this
    Happening, or macro won't work if the sheet
    Is empty or won't work twice if it's already been
    Run. Please advise.
  9. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi,

    Yes, it is actually good practice to prevent unwanted actions to be performed by accident. I suggest introducing some conditions in the code to stop it doing whatever you don't want it to do.

    You mentioned the empty sheet case... for this one you can take advantage of the fact that you know that "A2" (for instance) will be empty. Simply add the following to your SLA button code (at the begining):
    Code (vb):
    If Sheets("SLA").Range("A2") = "" Then
        Exit Sub
    End If
    This will exit the subroutine without doing anything when A2 is empty.
    The same applies to the second Clear button press... since the first press clears the contents of "A2", you can render a second press mute by checking if "A2" is empty (use the same code, provided above, at the beginning of the Clear button code).

    And so on... try to pinpoint the instances and use conditions in the code to prevent certain actions. If you have any trouble doing this just let me know and I will gladly try to help.

    Examples of some other simple ways to do this kind of stuff can be:
    Code (vb):
    On error resume next
    which will continue to run the code ignoring error (this is very useful when you know a certain error can be ignored)

    or

    Code (vb):
    on error goto "something"
    ...
    something:    ...rest of the code
    which allows you to jump over chunks of code, or the entire code if you wish, and continue from the specified point when an error occurs
  10. Tauqeer

    Tauqeer Member

    Messages:
    42
    Hi
    Mate , thanks for your help to fix this, really appreciate this, I have tried your code in both
    SLA and CLear macro and it worked perfect ....

    If Sheets("SLA").Range("A2") = "" Then
    Exit Sub
    End If

    now the only thing that is left , is it possible when the SLA macro runs and it creates the SLA column, if I press SLA button again then it runs SLA 2nd time
    and creates another SLA column as SLA1 , and it stuffs up everthing, is it possible that if the SLA column header is already there , then it will stop the SLA
    macro to run 2nd time, please advise.

    regards

    Tauqeer
  11. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi,

    Sure :)

    Following the same logic as before, something like this should work:
    Code (vb):
        If not Sheets("SLA").Rows("1:1").Find("SLA", lookat:=xlWhole) Is Nothing Then
            Exit Sub
        End If
    Add to the top of your SLA button code

    Hope this helps
    Tauqeer likes this.
  12. Tauqeer

    Tauqeer Member

    Messages:
    42
    I tried this code and it worked :

    If Sheets("SLA").Range("F1") = "SLA" Then
    exit sub
    end if
  13. Tauqeer

    Tauqeer Member

    Messages:
    42
    so whenever it detects the column header as SLA , it stops the macro and didn't run 2nd time, so it solves my problem...
  14. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi,

    Yes, that works too, as long as SLA is in "F1"... my approach does basically the same, except it looks for SLA in the entire first row which means you can have it anywhere and it will still work :)
  15. Tauqeer

    Tauqeer Member

    Messages:
    42
    Hi Pcosta

    Hope you are doing well. The macro that we resolved last time is doing well, few things I need to resolve, I am attaching the file so you can have a look, when I paste the data in the SLA sheet and the data in the col E is empty , then when the macro runs it still gives the green colour in Col F as per conditional formatting it should have given red colour as the SLA is not met, is there any way to fix it.
    2nd issue is if the issue is raised in Col D at 4:20 pm on 21st and resolved next day on 22nd at 9:30 am , Col D shows 17 hrs as SLA , clock should stop at 5:00 pm on 21st and then starts at 8:00 am next day so the SLA should be 2:10 in Col F. Is there anyway to fix it. I will attach the file as well so you can see. If we can make any changes in the macro then it will be great.

    Attached Files:

  16. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi,

    I'm good, thank you for asking :)
    I will need you to re-upload the file with the VBA unlocked... currently it is password protected.

    Thanks
  17. Tauqeer

    Tauqeer Member

    Messages:
    42
    Hi
    Actually password is just 1234....
  18. Tauqeer

    Tauqeer Member

    Messages:
    42
    here is the file without password...

    Attached Files:

  19. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi,

    I believe a simple IF condition can fix this!
    What would you like to show in "F" when "E" is empty?
    Notice that leaving "F" empty will return RED so it should be ok... but then we would need to think about "G" which is based on "F" and will return an error in this situation.
    Please advise!

    Fixed!
    If the day it was resolved is > then the day it was raised, it will subtract 15 hours... everything else remains the same.
    Now, let me know if there is a change the issue may only be solved 2 or 3 days after it was raised. In this case we would have to tweak the formula to subtract 15 for one day, 30 for 2 days and so on.

    Attached Files:

    Thomas Kuriakose likes this.
  20. Tauqeer

    Tauqeer Member

    Messages:
    42
    Thanks mate,
    If F is empty then it should bring Red as its unresolved, G col is the hidden one, if it brings error, can it be replaced with 0, is it possible or error will effect the pivot table, please advise.

    2nd issue is resolved as generally all SLA needs to be met on the same day , or the next day if it was logged late in the day, so it wont exceed to 2 or 3 days..

    I noticed it adds 0:00 at the bottom of col f , is it possible to get rid of this..

    thanks for your help..

    Attached Files:

  21. Tauqeer

    Tauqeer Member

    Messages:
    42
    Hi mate
    there is small issue I am facing with this SLA calculation, I am attaching this file,
    when there is a time like 10:40 AM to 11:40 AM , then it gives 1 with green colour which is correct, but when time is from 11:40 am to 12:40 pm, it gives SLA as 1 but the colour is red, which I don't understand , as conditional formatting should pick up one to make it green and anything over 1 should be red. Please advise as it can be a small hick up somewhere.

    Attached Files:

  22. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi,

    I just noticed I ended up never following up on your post from March, 31...
    I'm really sorry... I don't have email notifications active and I must have skipped that notification when I logged in :(

    Did you got it to work as you needed?

    As for today's post, I will need some time to look at it as I am a bit busy at the moment.

    Will get back as soon as I can.
  23. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi,

    If you look closely, although it is 1 hour, the value is higher than "0,0416666666666667" which is what you are using in the conditional formatting since it represents 1 hour / 24.

    If you format the cell that is seemingly giving an incorrect backgroud color to general, and increase the number of visible decimals, you will see that it is actually higher.
    Below I highlighted 2 cells that represent 1 hour but aren't exactly the same value if you show the exact value of the cell:
    1.JPG

    Perhaps you should round the results in that column and also in the conditional formatting so it ignores these little differences.
    Thomas Kuriakose likes this.
  24. Tauqeer

    Tauqeer Member

    Messages:
    42
    Hi Mate

    thanks for the tip, very useful, I will round the numbers to 0.042 to make it work and I will try that on the new data.

    Previous issue that we discussed about the Empty E Col.


    If E is empty then F should show Red Colour and G should reflect 0.

    Not sure how do we achieve that in conditional formatting. If it brings Error in G then pivot table won’t work. So I am confused on this. please advise.
  25. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi,

    Use an If condition in F: if E is empty then F = "" else "the formula you already have"
    In G, IFERROR return 0.
    In you code use the following for F (replace line 49 of module 3):
    Code (vb):
      ActiveCell.FormulaR1C1 = _
      "=IF(RC[-1]="""","""",[@[Date and time escalation was resolved]]-[@[Date and time escalation was received]])"
     
    and the following for G (replace line 123 of module 3):
    Code (vb):
        ActiveCell.FormulaR1C1 = "=IFERROR(IF([@SLA]*24<1,1,0),0)"
    On a side note, I noticed this sample isn't using some of the things we discussed earlier (the formula for when the issue is resolved in the next day, for instance).
    Thomas Kuriakose likes this.

Share This Page