Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am having a Pivot Table with Location,Name & Date as Dimension and Sum of a column Handled as expression.
Output is as given below.
Location | Name | Date | 2015-07-01 | 2015-07-02 | 2015-07-03 | 2015-07-04 | 2015-07-05 |
Bangalore | Agent 1 | - | 21 | - | - | - | |
Bangalore | Agent 2 | 491 | 1473 | 1104 | - | - | |
Bangalore | Agent 3 | - | - | - | - | - | |
Bangalore | Agent 4 | 500 | 653 | 488 | - | - | |
Bangalore | Agent 5 | 745 | 758 | 765 | - | - |
Need to add following columns at teh end.
Avg Per Day | Max | Min | # of days with > 500 |
How do i add this to the Pivot table as last columns post teh dates.
Please provide sample data or sample application. You need to use TOTAL Qualifier to achieve this result.
Hi,
If Date is also your dimension then i dont think so it is possible to do required thing in Pivot table.
You can try in Straight Table by creating each date value as different Expression.
Regards
Date | Handled | Name |
2015-07-01 | 267 | Agent 1 |
2015-07-02 | 254 | Agent 2 |
2015-07-02 | 321 | Agent 3 |
2015-07-02 | 330 | Agent 4 |
2015-07-03 | 396 | Agent 5 |
2015-07-04 | 258 | Agent 6 |
2015-07-06 | 253 | Agent 7 |
2015-07-06 | 315 | Agent 8 |
2015-07-06 | 319 | Agent 9 |
2015-07-07 | 254 | Agent 10 |
2015-07-07 | 294 | Agent 11 |
2015-07-08 | 252 | Agent 1 |
2015-07-08 | 253 | Agent 2 |
2015-07-08 | 271 | Agent 3 |
2015-07-08 | 361 | Agent 4 |
2015-07-09 | 286 | Agent 5 |
2015-07-10 | 385 | Agent 6 |
2015-07-13 | 260 | Agent 7 |
2015-07-16 | 260 | Agent 8 |
2015-07-17 | 306 | Agent 9 |
2015-07-17 | 315 | Agent 10 |
2015-07-17 | 366 | Agent 11 |
2015-07-20 | 253 | Agent 1 |
2015-07-20 | 272 | Agent 2 |
2015-07-20 | 311 | Agent 3 |
2015-07-20 | 347 | Agent 4 |
2015-07-22 | 297 | Agent 5 |
2015-07-23 | 253 | Agent 6 |
2015-07-23 | 309 | Agent 7 |
2015-07-23 | 463 | Agent 8 |
2015-07-24 | 266 | Agent 9 |
2015-07-25 | 272 | Agent 10 |
2015-07-26 | 253 | Agent 11 |
2015-07-26 | 291 | Agent 1 |
2015-07-26 | 335 | Agent 2 |
2015-07-26 | 418 | Agent 3 |
Please use the above sample data, Ignore location which i will add it later.
How do i change that dynamically.
Today i will have date till JUly 27th and tomorrow it will till 28th.
Do you mean i need to add expression default till 31st always.
Hi,
Yes,
Regards,
Use Straight Table
For Average
=SUM(TOTAL <Name> Handled)/COUNT(TOTAL <Name> Date)
For Min
Min(TOTAL <Name> Aggr(SUM(Handled),Name,Date))
For Max
Max(TOTAL <Name> Aggr(SUM(Handled),Name,Date))
For >= 500
SUM(TOTAL <Name> Aggr(IF(SUM(Handled)>=500,1,0),Name,Date))
HI,
In this method for the dates when there is no data available for an Name it takes previous days data and shows the same in Table.
How to avoid it?
Sorry It was my mistake in the expression i had mentioned <=