Table Chart Manipulation (RowNo)

[jorgett]

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

[rwunderlich]

I think it is fairly simple using TOTAL:

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

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[jorgett]

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

[rwunderlich]

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

[jorgett]

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

[jorgett]

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

Thanks,

JF

[rwunderlich]

Can you post your solution for future reference?

-Rob

[jorgett · Accepted solution]

Rob,

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

Best,

JF