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

    Manjula Raj

      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.

       

      Please help me to find the solution,

       

      Thanks in advance.

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

          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
            Andrew Watson

            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
              Andrew Watson

              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
                Manjula Raj

                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.

                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.

                 

                Please help me how to do this.

                 

                Thanks,

                Manjula

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

                  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
                    Mahfooj Khan

                    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
                      Manjula Raj

                      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
                        Mahfooj Khan

                        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
                          Manjula Raj

                          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
                            Mahfooj Khan

                            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
                              Manjula Raj

                              Hi Mahfooj,

                               

                              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.

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

                                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.

                                | Tableau Public

                                Feel free to ask If you've any query.

                                 

                                Mahfooj

                                • 13. Re: How to get the value of a particulat row count
                                  Manjula Raj
                                  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.

                                   

                                  I'm struggling to get this result. Please help me. Looking for your help.

                                  • 14. Re: How to get the value of a particulat row count
                                    Mahfooj Khan

                                    I think this is what you wanted. Workbook is saved in below mentioned link.

                                    | Tableau Public

                                    Let me know If you've any query.

                                     

                                    Mahfooj

                                    1 of 1 people found this helpful
                                    1 2 Previous Next