Grouping in a Pivot

[walterdp]

Hi all:

I'm having a difficult time to do this, so... need help!
Basically I have a table full of data. I need to group that data into certain time categories. Let me be clearer:

Each row (around 15000 +) has it's own timestamp and I need to group them like:


Count all that are -4 hours from NOW() (this means they missed SLA)
Count all that are -2 hours from NOW() (this means they missed SLA)
Count all that are +2 hours from NOW() (they will miss SLA in 2 hours)
Count all that are +5 hours from NOW() (they will miss SLA in 5 hours)
Count all that are +10 hours from NOW() (they will miss SLA in 10 hours)
etc

As of now I've created long formulas with countifs but they are taking long time for recalculating every time I make a change of any kind on any cell!

I've tried also with a Pivot Table, and grouping, but it does not work as expected.

Any idea??

 

[pete234]

What about an IF for each evaluating to 1 or "" and use SUM?
5 calcluated columns? This is just idea not exact.
IF(NOW-TIMESTAMP<4 HRS,1,"")
IF(NOW-TIMESTAMP<2 HRS, 1,"")
Etc
You will have to watch the time formats probably need decimal fraction of day.
The you would roll to the pivot as SUM with totals for each category.
Alternatively you could check out the information available on banding.

 

[Siraj.Samsudeen]

Hi Pete234,The calculated column approach will not work as the result of the calculations are materialized when you enter the formula and will not change unless you go and edit the formula again. So, we have to use a measure.

Hi walterdp, I am not familiar with the function to calculate the difference between 2 timestamps. If you can share some sample data and the long formula you wrote, I can try to see how to create a measure to do the same thing that your formula is doing. You need to use a switch statement in the measure formula to convert the time difference into a band of the hours that you have mentioned. Then, you can use the measure in a pivot table to group by different columns.

 

[walterdp]

Hi Siraj:

Thanks for replying. The formula i use to classify the rows (in this case only for rows that expire in 2 hours from NOW()) is:

=COUNTIFS('Workbook.xlsb'!Table_ExternalData_1[Assigned Group],$A$1,'Workbook.xlsb'!Table_ExternalData_1[INC Parent ID],"",'Workbook.xlsb'!Table_ExternalData_1[Category],"Incident",'Workbook.xlsb'!Table_ExternalData_1[SLA DUE Date],">="&$K$2,'Workbook.xlsb'!Table_ExternalData_1[SLA DUE Date],"<="&$K$2 + "02:00",'Workbook.xlsb'!Table_ExternalData_1[State],"New")+COUNTIFS('Workbook.xlsb'!Table_ExternalData_14[Assigned Group],$A$1,'Workbook.xlsb'!Table_ExternalData_14[INC Parent ID],"",'Workbook.xlsb'!Table_ExternalData_14[Category],"Event",'Workbook.xlsb'!Table_ExternalData_14[SLA DUE Date],">="&$K$2,'Workbook.xlsb'!Table_ExternalData_14[SLA DUE Date],"<="&$K$2 + "02:00",'Workbook.xlsb'!Table_ExternalData_14[State],"Open")

I have a cell with a similar formula for each category.

Then I tried to add this formula to each row

=IF([@[Actual Finish]]="",N(([@[SLA DUE Date]]- (NOW()-4))*24),-999999)

And then try to group the results in a pivot, but the values are too differents between each other, so I would need to make another kind of grouping or some other idea

 

[Laurent C]

@Siraj: I am not certain this is really a PowerPivot question.
Besides that, calculated columns should be fine: NOW() referring to the time the data was last updated makes more sense - at least to me - than getting the current time with old data.

@walter: The NOW function is volatile: every change you make in you spreadsheet will force the update of cells where the function is used. You can try to "cache" its result, but if you pool the data directly from an e-mail or CRM system through a SQL query, you might as well group your data directly in your query.

 

[walterdp]

Hi Laurent and all:

I believe I found the way. I'm using the
=IF([@[Actual Finish]]="",N(([@[SLA DUE Date]]- (NOW()-4))*24),-999999)
with trunc option (and the N function) and it gives some how good results, but the problem is that, on the Pivot Table, when I group values, although they are numeric, the sort is text:

Time to Expire
<-168
0-23
120-143
-120--97
144-167
-168--145
168-191
192-215
-24--1
24-47
-48--25
48-71
72-95
96-119

<tbody>
</tbody>

<tbody>
</tbody>

 

[Siraj.Samsudeen]

@Siraj: calculated columns should be fine: NOW() referring to the time the data was last updated makes more sense - at least to me - than getting the current time with old data.

I understand what you are saying Laurent. Calculated columns make more sense if you want your formulas to be refreshed only when data is refreshed. I was assuming that the the user wants to see the last-fetched values at the current time. Thanks for the clarification

 

[Siraj.Samsudeen]

but the problem is that, on the Pivot Table, when I group values, although they are numeric, the sort is text:

If this is a calculated column, please change the data type or the data format to numeric.

 

[walterdp]

Hi all:

The calculated column in the table is already in numeric format. and even in the Pivot appears as numeric. Also, when it is not grouped, it gets sorted from the smallest to the largest (numeric). Problem is when grouping.

 

[miguel.escobar]

@Siraj: I am not certain this is really a PowerPivot question.
Besides that, calculated columns should be fine: NOW() referring to the time the data was last updated makes more sense - at least to me - than getting the current time with old data.

@walter: The NOW function is volatile: every change you make in you spreadsheet will force the update of cells where the function is used. You can try to "cache" its result, but if you pool the data directly from an e-mail or CRM system through a SQL query, you might as well group your data directly in your query.

Not a powerpivot function but rather a table/pivottable question within excel.

Walter, the sorting part it's most likely the lack of a clear index in the actual column. Could you upload a sample of the file so we can test it out? also, you should try formatting those columns in order to get the best results.

best,
Miguel