Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pivot Table Formatting

Hi

I have been having issues when applying RAG formatting to a pivot table.

My table shows staff utilisation per month, and their respective teams.

The expression for the utilisation is as follows:

SUM([Billable AMS Time])/ SUM([Available AMS Time])*100


My Dimensions are Team, Person and Month(set horizontally)


When I have a filter on Month, and Department not expanded, the rule works as expected.


If I unfilter month, or expand Department, the rule appears for Department appears to use whatever value is in the top left cell for that Department.


See below:

Correct

correct.JPG



When I expand the department, however, it appears to take the top value for that department, as below:

incorrect1.JPG


If I remove the Month filter, I get a similar issue:

incorrect2.JPG



I would like the Department column's RAG to reflect the average of each person within that Department, and each Person's RAG to reflect their average across the three months.


Thanks in advance

1 Solution

Accepted Solutions
sunny_talwar

Try this:

=if(Aggr(NODISTINCT Avg(Aggr((SUM([Billable AMS Time])/ SUM([Available AMS Time])*100), Department, Resource, T_UploadMonth)), Department)<55, var_colour_RAG_Opaque_Red,

if(Aggr(NODISTINCT Avg(Aggr((SUM([Billable AMS Time])/ SUM([Available AMS Time])*100), Department, Resource, T_UploadMonth)), Department)>=70, var_colour_RAG_Opaque_Green,

var_colour_RAG_Opaque_Amber))

View solution in original post

6 Replies
sunny_talwar

Would you be able to share a sample to test this out?

Not applicable
Author

Certainly, find attached

Thanks

sunny_talwar

Can you check if this works for Department

=if(Aggr(Avg(Aggr((SUM([Billable AMS Time])/ SUM([Available AMS Time])*100), Department, Resource, T_UploadMonth)), Department)<55, var_colour_RAG_Opaque_Red,

if(Aggr(Avg(Aggr((SUM([Billable AMS Time])/ SUM([Available AMS Time])*100), Department, Resource, T_UploadMonth)), Department)>=70, var_colour_RAG_Opaque_Green,

var_colour_RAG_Opaque_Amber))

Capture.PNG

Not applicable
Author

Thank you.

It appears to work when the month filter is on, but when the month filter is removed it appears to error, and colour everything amber.

incorrect3.JPG

Definite progress though

sunny_talwar

Try this:

=if(Aggr(NODISTINCT Avg(Aggr((SUM([Billable AMS Time])/ SUM([Available AMS Time])*100), Department, Resource, T_UploadMonth)), Department)<55, var_colour_RAG_Opaque_Red,

if(Aggr(NODISTINCT Avg(Aggr((SUM([Billable AMS Time])/ SUM([Available AMS Time])*100), Department, Resource, T_UploadMonth)), Department)>=70, var_colour_RAG_Opaque_Green,

var_colour_RAG_Opaque_Amber))

View solution in original post

Not applicable
Author

Thanks Sunny, this appears to be working well!!