Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kira_whopper
Creator
Creator

Gradient color on pivot table based on each row

Hi there,

I'd like to format a gradient background color on a pivot table considering each row separately. For exemple, my goal is to show higher values in red, middle values in yellow and lower values in green, but those values are to be considered for each row separately (taking into account that a row here is assembled by the dimension "Assingment Group" and the Dates).

The final result for each row, using excel, would be this:

XlsSample.jpg

This is the expression on the pivot table:

Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number)

Attached is a sample.

Thanks in advance

1 Solution

Accepted Solutions
kira_whopper
Creator
Creator
Author

I think I found the solution, now I'm just playing to find the correct collors.

This was the expression to find the max per row:

Max(TOTAL <DeliveryCenter_AssignmentGroup>  {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>}

Aggr(

Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number)

, Task_RefDate, DeliveryCenter_AssignmentGroup)

)

Based on it, I just applied your concept of the Fractile, but using direct percentages. Now I just need to find the correct formula for the collors:

XlsSample2.jpg

View solution in original post

11 Replies
jwjackso
Specialist III
Specialist III

You can try something like this in the Background Color

=If(Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number) >= 25,

   RGB(155 + (100 * Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number) / 100),0,0),

     If( Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number) >= 15,RGB(255,155 + (100*(Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number)/15)),0),

     RGB(0,155 +(100 * (Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number)/10)),0)))

Background.PNG

kira_whopper
Creator
Creator
Author

I actually did something like this, but then the max a min wouldn't be based on the maximum value of the row, it would be based on a fixed value...
I'd have to do that, but based on the max and min values of the row...

For example, if the number of tickets increases, 25 should be green and will appear in red

jwjackso
Specialist III
Specialist III

Instead of hard coding the values like I did, you can use the Fractile function to calculate the value based on a percentage.

=If(Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number) >= Fractile(Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}>} Total Task_Number),50),

The Fractile will calculate the value that is 50% of the max.  This way the colors vary based on the total amount.  Say red above 50%, yellow above 25% else green.

The yellow does not vary much in shade.

fvelascog72
Partner - Specialist
Partner - Specialist

Hi,

Try this expression in Background Color:

ColorMix2(

  If(

  Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number)

  <

  Median(TOTAL <DeliveryCenter_AssignmentGroup> Aggr(Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number),Task_RefDate,DeliveryCenter_AssignmentGroup))

  ,

  (

  Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number)

  -

  Min(TOTAL <DeliveryCenter_AssignmentGroup> Aggr(Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number),Task_RefDate,DeliveryCenter_AssignmentGroup))

  )

  /

  (

  Median(TOTAL <DeliveryCenter_AssignmentGroup> Aggr(Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number),Task_RefDate,DeliveryCenter_AssignmentGroup))

  -

  Min(TOTAL <DeliveryCenter_AssignmentGroup> Aggr(Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number),Task_RefDate,DeliveryCenter_AssignmentGroup))

  ) -1,

  (

  Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number)

  -

  Median(TOTAL <DeliveryCenter_AssignmentGroup> Aggr(Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number),Task_RefDate,DeliveryCenter_AssignmentGroup))

  )

  /

  (

  Max(TOTAL <DeliveryCenter_AssignmentGroup> Aggr(Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number),Task_RefDate,DeliveryCenter_AssignmentGroup))

  -

  Median(TOTAL <DeliveryCenter_AssignmentGroup> Aggr(Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number),Task_RefDate,DeliveryCenter_AssignmentGroup))

  )

  ),

  Red(), Green(), Yellow()

)

Saludos.

kira_whopper
Creator
Creator
Author

I didn't managed to make it work. I'm stuck with the total by Group and Date. The fractile is returning the total of all items or the total for the current date only.

kira_whopper
Creator
Creator
Author

Thanks for the answer
I'm still checking your code to see what could be, but it didn't worked here...

kira_whopper
Creator
Creator
Author

I think I found the solution, now I'm just playing to find the correct collors.

This was the expression to find the max per row:

Max(TOTAL <DeliveryCenter_AssignmentGroup>  {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>}

Aggr(

Count(DISTINCT {<Task_Source = {'Incidents'}, Task_SourceType = {'Opened'}, Task_RefDate= {">=$(=Date(Max(Task_RefDate)-15))"}>} Task_Number)

, Task_RefDate, DeliveryCenter_AssignmentGroup)

)

Based on it, I just applied your concept of the Fractile, but using direct percentages. Now I just need to find the correct formula for the collors:

XlsSample2.jpg

antoniodneto
Creator II
Creator II

Hi Marco,

Could you help me? I have the same example to color each row.

My expression to calculate values is

(SUM(RISCO_C_11_14)+SUM(RISCO_D_15_30))

/

before(sum({1}RISCO_A_EM_DIA))


How can I create a gradient color following the same your conditions to higher,medium and lower values?

Tks a lot!!!

kira_whopper
Creator
Creator
Author

Hello Antonio, I believe you can use the solution above and just change my expression for yours.

If you're not able to do it, would be nice to have a sample of your application to look.