Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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.

IndexHoursResult
111
223
323
423
545.5
645.5
757
868.5
968.5
10810

 

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
Highlighted
Contributor III
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

 

 

View solution in original post

7 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Contributor III
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

Highlighted
MVP & Luminary
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

Highlighted
Contributor III
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

Highlighted
Contributor III
Contributor III

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

Thanks,

JF

Highlighted
MVP & Luminary
MVP & Luminary

Can you post your solution for future reference?

-Rob

Highlighted
Contributor III
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

 

 

View solution in original post