Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Øystein_Kolsrud
Employee
Employee

Expressing measure the refers to values from different row of table

Hi! I have the following table:

Key
Entry
0x
1x
2x
1y
2y

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:

KeyEntries for Key
Entries for previous Key
010
121
222

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?

1 Solution

Accepted Solutions
sunny_talwar

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.

View solution in original post

16 Replies
sunny_talwar

Not sure I completely understand, but may be this

Above(Count(ENTRY))

OmarBenSalem

Above(count(Entry)):

Capture.PNG

Øystein_Kolsrud
Employee
Employee
Author

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.

sunny_talwar

You can do Below(Count(ENTRY)) if you want to keep this descending

Øystein_Kolsrud
Employee
Employee
Author

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.

sunny_talwar

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.

Øystein_Kolsrud
Employee
Employee
Author

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)"?

sunny_talwar

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)))

OmarBenSalem

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

];