Create variable from calculating the values of a table

[limu]

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!

[UseCodeMeow]

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 🙂