How to count color values in different columns in a one row

[pra_kale]

Hi Team,

I am developing one application in which i want last 3 columns which provides count of values in colors.

For e.g. I have created a Pivot table with 10 columns. From that in 1 row

i) 2 columns values are in "Red color",

ii) 5  columns values are in "Blue Color",

iii) and 3 columns values are in "Green color". 

So final last 3 columns with Name RED, BLUE and GREEN  will provide the count how many values are in Red, Blue and in Green color. In above example In Red values are  2, In Blue values are 5 and In green color values are 3.

Can you pl help how I can achieve this in qlik sense.

Thanks in advance.

[rubenmarin]

Hi, I think th eonly option is to reproduce the condicitons to count when a column is red, blue or green in the last 3 columns, you can use rangesum to sum all conditions, ie for red column it can be:

RangeSum(
  If([Column1]<[ObjMin1], 1,0)
  ,If([Column2]<[ObjMin2], 1,0)
  ...
  ,If([Column10]<[ObjMin10], 1,0)
)

[pra_kale]

Hi,

Thanks Rubenmarin for your help.

In condition where you have referred  [Column 1] that means here I have to put entire condition written for Column 1 and so on till the column 10 I have to put.

Thanks in advance

[rubenmarin]

Yes, there are 3 options:

- Use the same expression used in that column: If(Sum(...

- Refer to the column by number using Column() function: If(Column(1)...

- Refer to the column by column label: If([ColumnLabel]...

[pra_kale]

Hi,

Thanks..

But just for knowledge .. if I put above same condition in all 3 columns RED , BLUE and GREEN then how the condition identify that in RED column give the Count of figures which are in RED only and in BLUE column to give the count of figures which are in BLUE only.. and similarly in Green column.

[rubenmarin]

Hi, you need to redroduce the conditions set for red, blue or green, so if one column is red because it's value is below 50% you can use:

RangeSum(
  If([Column1]<0.5, 1,0)
  ,If(Column(2)<0.5, 1,0)
  ...
  ,If([ExpressionForColumn10]<0.5, 1,0)
)

[pra_kale]

Hi,

In condition we are checking particular channel total against a total of that column. So if any particular channel total is less than overall total , i.e. company total which includes all channels put together, then for that channel value for said KPI is in RED, if it is more than overall total then Value is in green and if it is above 90 and less than 100 then the value is in Blue. This conditions we are already set in our 10 KPI's expression and that is working fine.

But now only challenging part remains is where we want the count in last 3 columns of RED, BLUE and GREEN. From these 10 KPI's how many are in Red, How many are in Blue and how many are in Green.  e.g  4 are in RED, 3 are in Blue and 3 are in Green.

Thanks in advance.

[rubenmarin]

What are you using to set KPI in RED or BLUE? font color of a table?