1 2 Previous Next 17 Replies Latest reply on May 30, 2016 11:55 AM by Manjula Raj

# How to get the value of a particulat row count

Hi,

How to get the PNL from 500th count of row. I've a history table where user has many charts and for each chart they have pnl. Total charts will be 2000.  I want to calculate the user pnl at 500,1000,1500,2000 charts. How to calculate this?

As chart id is random i can't take the chart_id for getting the exact point of pnl so if we take COUNT(chart_id) <= 500 then how to get the pnl from last row ? I use "<=" bcoz all user will not have answered 500 charts, they may be vary from 1 to 500. Suppose user has only 10 charts then i want to get the pnl of 10th row. How to do this.

• ###### 1. Re: How to get the value of a particulat row count

I don't know about your data structure. It will be helpful If you share some dummy data.

However I thing you can use INDEX() or SIZE()

Create a calculated field using INDEX() OR SIZE()

IF INDEX()<=500 THEN [Pnl] END

IF size()<=500 THEN [Pnl] END

Let me know If this helped. Or share the dummy data or your sample workbook in .twbx fromat with your expected output.

Mahfooj

1 of 1 people found this helpful
• ###### 2. Re: How to get the value of a particulat row count

Agree with Mahfooj you would need to make use of INDEX to give you a count of charts. To find the latest (max) chart you would use a WINDOW_MAX() on your INDEX calculated field.

Then you could use that WINDOW_MAX field to either pull in chart 500 or the latest, if there aren't 500 charts for that user. That calculation, which you would put on your filter shelf and set to True, could be [IndexField]  = IF [WindowMaxField] > 500 THEN 500 ELSE [WindowMaxField] END

• ###### 3. Re: How to get the value of a particulat row count

A question using these techniques was recently answered, which may help: Re: Restricting number of rows to show on bargraph

• ###### 4. Re: How to get the value of a particulat row count

But the solution you gave is not worked for me. Here i'm giving the details of my sample data and what i want to achieve.

I've attached the history table here. From this table i need to get the result. This history table has relationship with User.Each user has many charts and for each chart they have pnl.

I wan to show the result as showed below and attached the original tableau page histroy.png.

IDUser500 Charts1000 charts1500 charts2000 charts
1Manjula 1pnl in the 500th rowpnl in the 1000th rowpnl in the 1500th rowpnl in the 2000th row
2Manjula 2
3Manjula 3
4Manjula 4
5Manjula 5

If Manjula 1 has only 100 charts then i want to show the pnl of 100th row. If 500 then 500th row.

I did the calculation as below: Chart id is random for all users so using the count of chart_id

pnl of 500 :  IF size()<=500 THEN AVG[Pnl] END

IF COUNT([Chart Id]) <= 500

THEN AVG([Pnl (Stats)]) // this condition works good as i've final pnl saved in stats table

ELSEIF COUNT([Chart Id]) >=501

THEN [pnl 0f 500]

else

0

END

the result in the elseif part gives the average[pnl] of the pnl. which i dont want i want the exact point pnl.

Thanks,

Manjula

• ###### 5. Re: How to get the value of a particulat row count

Thanks Andrew for the reply.

Can you please elaborate more. What field and what filter needs to create? Sorry i'm not expert in tableau.

• ###### 6. Re: How to get the value of a particulat row count

I have a question, saw you data set can you explain is that what you wanted?

As for example User id 2 has 10 charts and its <=500 so you want pnl of last row i.e. 221.

Please correct If I'm wrong

• ###### 7. Re: How to get the value of a particulat row count

Yes, you are right. I want the pnl  of last row  i.e 221.

• ###### 8. Re: How to get the value of a particulat row count

Then try this and let me know

1 of 1 people found this helpful
• ###### 9. Re: How to get the value of a particulat row count

Thank you it worked to find the last row.

Can you please tell me how to apply this for chart count 500,1000,1500,2000?

• ###### 10. Re: How to get the value of a particulat row count

Actually I'm bit confused about the logic of Charts counts. As per your requirement charts count logic is like that? Kindly confirm

500 charts: count(chart id)<=500

1000 charts: count(chart id)>500 and count(chart id)<=1000

1500 charts: count(chart id)>1000 and count(chart id)<=1500

2000 charts: count(chart id)>1500 and count(chart id)<=2000

The history table which you've shared has only 723 records. Based on user id I haven't seen any user who has more than 500 charts. So If you see below attached screen shot then you'll find only 500 charts column can be shown. May be in your original data you've complete set of records. You can try like this.

You've to create another calculated field for 1000 charts just modify the logic.

Correct me If I'm wrong.

• ###### 11. Re: How to get the value of a particulat row count

Hi Mahfooj,

You are right with the logic of chart count. Sorry, the data which i gave is local server data. On production is has more than lac of rows.So i couldn’t give that data. I'll explain bit more to understand my problem.

We are developing a gaming software for trading students. We have total 2000 charts in the db. So each user has to answer all these charts. Each chart has its own pnl, depending on the user's correct and wrong answer pnl will be goes up or down. We want to track the progress of the user by their pnl at 500, 1000,1500, 2000 charts.

So i want to show the result for each users pnl at these 4 points. As charts are random to users i can't track it by chart_id.

I know my sample data has not enough records but please help me with low count levels. Like in sample max chart count is around 290. You can explain me with the count 50,100,150,200. I need to get the pnl of the user  at 50th charts next at 100th chart, so on.

• ###### 12. Re: How to get the value of a particulat row count

I've found something like this.

Logic I've used

50 charts: count([chart id])<=50

100 charts: count([chart id])>50 and count([chart id])<=100

150 charts: count([chart id])>100 and count([chart id])<=150

200 charts: count([chart id])>150

If you see most of the users falls under 50 chart counts. There is one user who falls under 100 chart counts and there is no user in 150 chart counts

but two users are having more than 150 charts.

I've saved the workbook (public version 9.3) in my profile, link has been mentioned below. Have a look and let me know If this help.

Feel free to ask If you've any query.

Mahfooj

• ###### 13. Re: How to get the value of a particulat row count
user idchart countlast pnl50 charts100 charts150 charts200 charts
1367676
55302106501426106
6551668866

Thank you Mahfooj for your effort to make a workbook.

But this is not the result i'm looking for. I've got the result page like this. Actually i need it like above table.

I got the above result from sql queries.

ex: select * from history where user_id=55 limit 50; # last row pnl is 50

select * from history where user_id=55 limit 100; #last row pnl is 14

I need  something like this.

See in your workbook user_id 55 has chart_Count  302.  It is showing only its last pnl in the 200 charts column. But i want it in all 50 ,100,150 and 200 charts column. The result in 50 charts column should be the pnl when user_id  55 completed his first 50 charts, then on when he completed 100 charts and so on for 150 and 200 charts.