6 Replies Latest reply on Aug 4, 2016 5:11 AM by John Doe

    How do I filter the underlying data?

    John Doe

      Hi. I have a worksheet that looks like this, including the underlying data:

      What I need is to only count the first occurrence of [filmstamdata_id]. Right now, the years across gives me the count of [filmstamdata_id] per year. But I need to count it across all years. So taking the marked lines as example, should only count as 1 in 2006, and 0 in 2007.

       

      Right now, CNTD gives me distinct counts per year, but not across the entire range of years.

       

      How do I achieve this?

        • 1. Re: How do I filter the underlying data?
          Luciano Vasconcelos

          You don't achieve this by underlying data.

          Underlying data is the raw data used to generate your analysis.

          I believe you need to build another view and use the summary data.

          • 2. Re: How do I filter the underlying data?
            Carl Slifer

            Hi John Doe,

             

            If that is your real name...

             

            Try the following forumula:

            {FIXED [filmstamdata_id]: MIN([modtaget_dato])} = [modtaget_dato] 

             

            What this will do is look at each filmstamdata_id and find the first modtaget_dato in your underyling data. Whatever the first value is (for each filmstamdata) will be compared to every row. If the above calculation returns true then you can keep it with a filter else you would remove it. This would remove any row that wasn't the first date. However if you still have some instances where you have two rows with the same filmstamdata_id and modtaget_dato then you will return both of those rows. However your COUNTD(filmstamdata_id) Should still work fine!  Just be aware that other values that you use might be doubled in this case.

             

            Best Regards,

            Carl Slifer

            InterWorks

            1 of 1 people found this helpful
            • 3. Re: How do I filter the underlying data?
              Andrew Watson

              This would be easier if you could provide a twbx. I can only give suggestions for what should work but can't test without the workbook. This should be achievable by a table calculation. You want to identify the first instance of each filmstamdata_id and count it.

               

              A formula such as : IF FIRST() = 0 THEN 1 END might work. You'll need to go into the the advanced options to calculate the first, you want this to restart every filmstamdata_id. if this works correctly a 1 should appear against the first instance of each id and a null against the others. Therefore to do the count just SUM this calculated field.

               

              It is likely to also be possible using an LOD formula. Something like: IF [modtaget_dato] = {FIXED [filmstamdata_id]:MIN([modtaget_dato])} THEN 1 END. As above, if this works, just SUM this field.

               

              I haven't tested either of these but should put you on the right track. If neither work please provide a twbx.

              • 4. Re: How do I filter the underlying data?
                John Doe

                Thank you, this fixed my problem. I am not sure where I could have "some instances where I return two rows" - but it does not seem to be the case here...

                • 5. Re: How do I filter the underlying data?
                  John Doe

                  I think I see where you're getting at, and I will remember to attach a workbook in the future. But Carl's suggestion helped me quite. Thank you for taking your time to reply.

                  • 6. Re: How do I filter the underlying data?
                    John Doe

                    Can you help me a little more with this calculated field? I need it not to be:

                    {FIXED [filmstamdata_id]: MIN([modtaget_dato])} = [modtaget_dato]

                     

                    But rather,

                    {FIXED [filmstamdata_id]: MIN([modtaget_dato])} = [modtaget_dato] IF [stoette_ordning_id] is equal to "current stoette_ordning_id" being filtered.

                     

                    So if you look at the screenshot, you see a modtaget_dato for 14-09-2006 and 05-05-2007 - the first_modtaget_dato calculated field should return 14-09-2006 - but there are other rows in this query that have a modtaget_dato earlier than 2006, but they have a different stoette_ordning_id and thus is returned by the calculated field you supplied. Makes sense?