• 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...

  • 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

Tauqeer

Member
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
 

Attachments

  • Esc_SLA Data trial.xlsm
    375.1 KB · Views: 6
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
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
 
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
 
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.
 

Attachments

  • chandoo32640Esc_SLA Data trial.xlsx
    149 KB · Views: 4
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
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"
 

Attachments

  • Esc_SLA Data trial.xlsm
    383.8 KB · Views: 7
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"

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
 

Attachments

  • Esc_SLA Data trial 2.xlsm
    446.7 KB · Views: 7
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
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:
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:
CopyOrigin:=xlFormatFromRightOrBelow
instead of
Code:
CopyOrigin:=xlFormatFromLeftOrAbove
which would copy the format of the column on the right instead of the left
 
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.
 
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.
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:
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:
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:
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
 
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
 
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
Hi,

Sure :)

Following the same logic as before, something like this should work:
Code:
    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
 
so whenever it detects the column header as SLA , it stops the macro and didn't run 2nd time, so it solves my problem...
 
I tried this code and it worked :

If Sheets("SLA").Range("F1") = "SLA" Then
exit sub
end if
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 :)
 
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.
 

Attachments

  • Esc_SLA Data 21 Mar 2017.xlsm
    643.3 KB · Views: 5
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.
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
 
Hi,

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

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!

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.

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.
 

Attachments

  • Esc_SLA Data 21 Mar 2017.xlsm
    685 KB · Views: 7
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..
 

Attachments

  • upload_2017-3-31_20-3-23.png
    upload_2017-3-31_20-3-23.png
    603 bytes · Views: 2
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.
 

Attachments

  • Esc_SLA Data-1.xlsm
    616.5 KB · Views: 5
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.
 
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.
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.
 
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.
 
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.
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:
  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:
    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).
 
Back
Top