6 Replies Latest reply on Sep 5, 2017 2:15 AM by Emanuele Marcandella

    Finding In and Out values count

    Teja G

      Hi All,

      I have a requirement here.

      lets assume we have three weeks of data.

      1st week Data Values - 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

      2nd week  Data values- 1, 2, 3, 4, 5, 11, 12, 13, 14, 15

      3rd Week Data Values - 11, 12 ,13, 14, 15, 16, 17, 18, 19, 20

       

      So from the above data, i want a calculation which gives me how many records are in and how many are out from the week( e.g 1st week values in - 10 , second week in -5 (11,12,13,14,15) and out 5 (6 , 7 , 8,  9, 10) .i want two columns like in and out and values should be 5 and 5.

      How can we achieve that with a single calculated field? any reply with solution or ideas are highly appreciated.

        • 1. Re: Finding In and Out values count
          Emanuele Marcandella

          Hi BG,

          I'm trying to understand your request but to be honest I didn't.

           

          What do you mean with IN or OUT?

          Why in the second week 6, 7, 8, 9, 10 are out? Because they are already present in the first?

          In the 3rd week IN (16, 17, 18, 19, 20) and OUT (11, 12, 13, 14, 15)?

          • 2. Re: Finding In and Out values count
            Teja G

            Hi Emanele,

             

            Lets assume like this. First week we have 10 incidents in our queue.

            second week 1 to 5 incidents are still open and 6 to 10 incidents are closed. again 11 to 15 incidents added(new).

             

            What do you mean with IN or OUT? --

            Here In means 11 to 15 records (total count - 5)

            Out means

             

            Why in the second week 6, 7, 8, 9, 10 are out? -- Because those are out of queue

            Because they are already present in the first? -- No

             

            In the 3rd week IN (16, 17, 18, 19, 20) and OUT (11, 12, 13, 14, 15)? --16 to 20 are added to queue and 11 to 15 are closed.

             

            Here i want something like

             

                                 In    Out

            week 1       10   0

            week 2         5   5

            Week 3        5   0

             

            Hope you can understand the problem statement better now.

            • 3. Re: Finding In and Out values count
              Emanuele Marcandella

              Ok, now I understand the environment (incidents) so I easily understand what you need!

               

              Even if... I'm sorry but I need some other clarification (I'm feeling a dumb right now) because I thought that I understood well but maybe not.

               

              Week1

                   NEW INCIDENTS: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 - because all of them are new and never seen

               

              Week2

                   NEW INCIDENTS: 11, 12, 13, 14, 15 - because all of them are new and never saw before

                   OPEN INCIDENTS: 1, 2, 3, 4, 5 - because there were in the list and are present yet

                   CLOSED INCIDENTS: 6, 7, 8, 9, 10 - because there were in the list and now are no more present

                 

              Week3

                   NEW INCIDENTS: 16, 17, 18, 19, 20 - because all of them are new and never saw before

                   OPEN INCIDENTS: 11, 12, 13, 14, 15 - because there were in the list and are present yet

                   CLOSED INCIDENTS: 1, 2, 3, 4, 5 - because there were in the list and now are no more present

                 

              I thought that you need to trace how many are NEW and CLOSED (so In/Out)... but with this condition the result should be

               

                             IN     OUT

              week1     10     0

              week2     5     5

              week3     5     5

               

               

              What am I missing!?!? (sorry!!)

              • 4. Re: Finding In and Out values count
                Teja G

                that's correct Emanuele, i missed it. that is exactly my requirement i need that In and Out values to be displayed (as in your response). i want to have a calculated filed that should give In and Out values.

                • 5. Re: Finding In and Out values count
                  Emanuele Marcandella

                  Hello TG,

                   

                  i thought that I could help but I think that this is over my knowledge and skills.

                  I found this topic very similar to yours I think:

                  I need to create a calculated field that would check if the value in Column 2 exists in Column 1, if it does then return �yes�. (Without blending or duplicating the data)

                   

                  I tryed to apply this to a file with your data but the issues I find are related to the date... becase If you have two column it's "easy", but if they increase it's difficult.

                   

                  I hope that someone else can help you!!

                  • 6. Re: Finding In and Out values count
                    Emanuele Marcandella

                    By the way, as you can see, even the expert have difficulties with this kind of questions. I think that there is a "Tableau limitation". I suggest you to do that in excel if you can.