Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I'm trying to create a variable that contains the value of a SUM aggregation. I want to calculate the percentage of rows that contain some value. I want it to solve as (# of rows having value 'x' / # of all rows) to get the percentage of entries that have the value 'x' for example, on some specific field.
How would I calculate it? To make the example more concrete, say I have the following table called 'TableA', that has fields 'Field1' and 'Field2'. How can I create a variable 'vPercentageX' that contains the percentage of rows in 'Field1' that have the value 'x'.
Thank you in advance!
Hello,
I'm not sure on what is the exact approach that you need. However, this is what I did.
For example, if the data looks like this:
[A]:
LOAD * INLINE [
Field1, Field2
x, 115
y, 890
x, 11
x, 78
x, 88
y, 39
];
Then in your variable, place this formula: COUNT({$<Field1={'x'}>}Field1)/COUNT(TOTAL Field1)
Then in your chart, place this as =$(vPercentageX) to display the result. You can use the Number format of '##.##%' in Qlik or you can use this NUM(COUNT({$<Field1={'x'}>}Field1)/COUNT(TOTAL Field1),'###.##%')
For my next assumption, for example the data looks like this:
[B]:
LOAD * INLINE [
FieldA, FieldB
x, 115
y, 890
Box, 11
x, 78
x, 88
y, 39
];
and you want to get those values with x in it such as Box. In your variable, you can place this:
COUNT({$<FieldA={"*x*"}>}FieldA)/COUNT(TOTAL FieldA).
Change count to Sum if your target is a numerical value. Meanwhile, * is a wildcard so that any value that contains x in it will be counted.
I hope this helps 🙂