
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Create variable from calculating the values of a table
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!
- Tags:
- script

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 🙂
