CODEWITHSUNDEEP
qlikviewqliksense
Dur
Dur

Reputation: 1

Value as a % of row total in QlikSense

In QlikSense i want to show value as % of row total, using:

count(-value-) / count(Total -value-)

Using the expression above gives me % of totals not row total.

data example

For example, in the new type column, T, % should be 7935 / 8287 = 95.75% and not 13.71%

count(new_type_id) / count(Total New_Type_ID) gives me the result in attached picture

Upvotes: 0

Views: 1194

Answers (1)

Stefan Stoychev
Stefan Stoychev

Reputation: 5012

Im using the following script to recreate your data:

Load * inline [
Type, New_Type_ID, new_type_id 
T   , 8287       , 7935
B   , 11942      , 565
C   , 18233      , 674
X   , 13890      , 165 
P   , 5515       , 0
];

And using the following expression:

Sum(new_type_id) / Sum(total <Type> New_Type_ID)

The result will show 95.75% for Type = T

result table

The difference is in the scope of the Total qualifier. In your case Total will be called over all results/values and will yield 57 867.

Total can be "forced"/scoped to be called only over specific field(s) values:

Total <[field name here]>

From Qlik's documentation page

docs

Update

(as pivot table and split)

Load script to achieve pivot table structure:

Load * inline [
Type, Amount
T   , 8287
B   , 11942
C   , 18233
X   , 13890
P   , 5515 
];

Load * Inline [
New_Type, Type, SplitAmount
T       , T       , 7935
B       , T       , 332
C       , T       , 12
X       , T       , 6
P       , T       , 2
T       , B       , 565
B       , B       , 11022
C       , B       , 302
X       , B       , 45
P       , B       , 8
];

pivot table

Measures:

  • Units calculationSum(SplitAmount)
  • % calculation Sum(SplitAmount) / Sum(Total <Type> SplitAmount)

Upvotes: 0

Related Questions