1 of 1 people found this helpful
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.
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
Thank you Mahfooj for your reply.
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.
ID User 500 Charts 1000 charts 1500 charts 2000 charts 1 Manjula 1 pnl in the 500th row pnl in the 1000th row pnl in the 1500th row pnl in the 2000th row 2 Manjula 2 3 Manjula 3 4 Manjula 4 5 Manjula 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]
the result in the elseif part gives the average[pnl] of the pnl. which i dont want i want the exact point pnl.
Please help me how to do this.
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.
Yes, you are right. I want the pnl of last row i.e 221.
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?
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.
Thank you very much for your reply. I was waiting for your reply.
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.
Please help me. I'm really not getting the idea.
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.
user id chart count last pnl 50 charts 100 charts 150 charts 200 charts 1 36 76 76 55 302 106 50 14 26 106 65 51 66 88 66
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.
I'm struggling to get this result. Please help me. Looking for your help.