# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for
Did you mean:
Contributor III

## Table Chart Manipulation (RowNo)

Hello,

Please help me with the following. In the table below, I have columns "Index", which is the function RowNo(total), and "Hours". I am interested in getting the column "Result". Essentially, "Result" is "Index", but the difference is where the observations "Hours" have the same value, I sum their corresponding "Index" and then divide by the number of "Index".

For instance, for "Hours" 2, the frequency is 3. We do: (2+3+4)/3 = 3. For "Hours" 4: (5+6)/2=5.5. For "Hours" 6: (8+9)/2=8.5. All in all, I want to come up with an expression(charts only, no script) where I use the first two columns to get the third column.

 Index Hours Result 1 1 1 2 2 3 3 2 3 4 2 3 5 4 5.5 6 4 5.5 7 5 7 8 6 8.5 9 6 8.5 10 8 10

Here is what I have done so far, but has not been working as desired:

if (pick(count(total [Hours]) - rowno(total) + 1, \$(=concat([Hours], chr(44), -[Hours]))) =
pick(count(total [Hours]) - rowno(total) + 2, \$(=concat([Hours], chr(44), -[Hours]))), rowno(total)+rowno(total)-1,
rowno(total))

Thank you for your time and help.

JF

1 Solution

Accepted Solutions
Contributor III

Rob,

Sure! I ended up using the rank function. Rank(-Hours, 2) will get you the column "Result" in the table above.

Best,

JF

7 Replies
MVP & Luminary

I think it is fairly simple using TOTAL:

sum(TOTAL<Hours>Index) / count(TOTAL<Hours>Index)

Contributor III

Rob,

That line of code does not get me the "Result" column.

FYI I am using rowno(total) as the "Index" column. And please note, for the "Hours" values that do not have duplicates, their indices remain untouched.

JF

MVP & Luminary

In your initial post you said "In the table below, I have columns "Index" and "Hours"".   Then you said you are using RowNo(TOTAL) as Index.  Is that just for example or do you want your solution to use RowNo(TOTAL)?

-Rob

Contributor III

RowNo(total) is what I am using to get the indices. So yes, I would like it to be part of my solution.

Thanks,

JF

Contributor III

Please ignore the question. I have solved the problem. I will delete the post once I find out how to.

Thanks,

JF

MVP & Luminary

Can you post your solution for future reference?

-Rob

Contributor III

Rob,

Sure! I ended up using the rank function. Rank(-Hours, 2) will get you the column "Result" in the table above.

Best,

JF

Tags