Create Pivot table?

[#1]

Hi everyone,

I need to create a table that looks similar to the following using a table called Service Desk which holds call details. I have fields called Status ,CreatedTime, etc.

Status	0-30 Days	30-60 Days	60-90 Days	90+ Days	Total
Open	5	5		13	23
Onhold	2		5	5	12
Awaiting User	5	5		13	23
In Progress	2			13	15
3rd Party	5		5	13	23
Awaiting HCL	2	5		13	20
Awaiting Business	2		5	13	20
Awaiting Change Control	2	5		13	20
Awaiting Development	2			5	7
27	20	15	101	163

1.Can this be achieved using a pivot table chart?

2.How do I display the number of calls based on the days they've been open for? Is this achievable using expressions in the pivot table?

like:

=if((today() - CreatedTime) >= 0 AND (today() - CreatedTime) <= 30, count([Request ID]))

=if((today() - CreatedTime) > 30 AND (today() - CreatedTime) <= 60, count([Request ID]))

3. How do I display the sub totals at the end of the rows and columns like in the above table?

Thanks.

[sudeepkm · Accepted solution]

this can be done in Pivot table.

1. create the flag field [0 - 30Days and so on ] by writing the logic in the load script

2. Create a Pivot table add dimensions Status and DurationFlag and add expressions as count of tickets/calls

3. Drag the Duration Flag to top right and then enable "show partial Sums"

[maxgro]

see attachment for some idea if you want to do it in the chart