Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi! I have the following table:
Key | Entry |
---|---|
0 | x |
1 | x |
2 | x |
1 | y |
2 | y |
Now I want to create a visualization that contains the number of entries per Index as well as the number of entries for the previous index, so the result should look like this:
Key | Entries for Key | Entries for previous Key |
---|---|---|
0 | 1 | 0 |
1 | 2 | 1 |
2 | 2 | 2 |
The measure for the first measure I could easily define like this:
COUNT(ENTRY)
But I am having a very hard time expressing the second one. How would I express that measure?
The work around that is to use Aggr() function. Do you know if the data is sorted ascending or descending when loaded into QlikView? If it is, then you can do something like this
1) If Key is in Ascending order
Aggr(Above(Count(ENTRY)), Key)
2) If Key is in Descending order
Aggr(Below(Count(ENTRY)), Key)
Now when you sort, the values will not change.
Not sure I completely understand, but may be this
Above(Count(ENTRY))
Above(count(Entry)):
Thanks for the quick reply, but that doesn't really seem do what I want. If I do like that and sort on Key descending, then I will not get the correct values. I would still want the Key 1 to have the value 1 in the second column (since key 0 has only 1 value), but using "above" I get 2.
You can do Below(Count(ENTRY)) if you want to keep this descending
But then I assume it won't work if I order it ascending? The point is that I don't want the second measure to be affected by the order in which the rows of the table are listed.
The work around that is to use Aggr() function. Do you know if the data is sorted ascending or descending when loaded into QlikView? If it is, then you can do something like this
1) If Key is in Ascending order
Aggr(Above(Count(ENTRY)), Key)
2) If Key is in Descending order
Aggr(Below(Count(ENTRY)), Key)
Now when you sort, the values will not change.
But the keys still have to come in order. What if I ordered the table on the second column (the one with just "Count(Entry)"?
Doesn't matter, as long as Key is sorted ascending or descending in the script, you are all good to go. Sort your chart in whatever way you want.
Actually, I just saw you use Qlik Sense. Try this expression
Aggr(Above(Count(ENTRY)), (Key,(NUMERIC)))
try to create a recno() in the script then use this expression : aggr(Aggr(Above(Count(Entry)),Key),recno)
in the script, here is what I have:
Load RecNo() as recno,* Inline [
Key ,Entry
0, x
1, x
2, x
1, y
2, y
];