4 Replies Latest reply on Dec 17, 2014 8:40 AM by KK Molugu

    Filter based on aggregated value for each dimension member

    Chris Berger

      Hi guys,

       

      I've trying to solve the following problem: need to keep dimension member where the aggregated measure SUM(factTotal) > 0 in at least 5 consecutive periods (columns). For example take a look at the image below. Dimension idCliente equals to 307714 has only 2 dimension member not zero, therefore it should be filtered. On the other hand 307717 has values in 7 periods and it should be kept.

       

      Screen Shot 2014-12-15 at 4.33.52 PM copy.png

      TWBX file attached:

       

      cheers!

        • 1. Re: Filter based on aggregated value for each dimension member
          Michael Carper

          Try this table calculation on for size. The downside if that you have to hand-code the row offset (ie it looks at periods 0-4, or 1-5, or 2-6). If you have lots of periods, this might become cumbersome, and it might be easier just to edit your custom SQL to achieve the same functionality.

           

          IF

          (LOOKUP(SUM([factTotal]), FIRST()) > 0

          AND LOOKUP(SUM([factTotal]), FIRST()+1) > 0

          AND LOOKUP(SUM([factTotal]), FIRST()+2) > 0

          AND LOOKUP(SUM([factTotal]), FIRST()+3) > 0

          AND LOOKUP(SUM([factTotal]), FIRST()+4) > 0)

          OR

          (

          (LOOKUP(SUM([factTotal]), FIRST()+1) > 0

          AND LOOKUP(SUM([factTotal]), FIRST()+2) > 0

          AND LOOKUP(SUM([factTotal]), FIRST()+3) > 0

          AND LOOKUP(SUM([factTotal]), FIRST()+4) > 0

          AND LOOKUP(SUM([factTotal]), FIRST()+5) > 0)

          )

          OR

          (

          (LOOKUP(SUM([factTotal]), FIRST()+2) > 0

          AND LOOKUP(SUM([factTotal]), FIRST()+3) > 0

          AND LOOKUP(SUM([factTotal]), FIRST()+4) > 0

          AND LOOKUP(SUM([factTotal]), FIRST()+5) > 0

          AND LOOKUP(SUM([factTotal]), FIRST()+6) > 0)

          )

          THEN

          'Include' ELSE 'Exclude' END

          • 2. Re: Filter based on aggregated value for each dimension member
            KK Molugu

            Chris Berger

             

            This can be solved using a Table calculation (Window_Sum) on the marks that is > 0 FactTotal. Once you have that, you can use that to filter where the sum of records > 0 is at least 5. Hope this helps.

             

            // Calculation

            window_sum(if zn(sum([factTotal])) > 0 then 1 else 0 end)

            153472-kk.PNG

             

            Michael Carper

            With that calculation, as you said ,it would be cumbersome.

            ..kk

            • 3. Re: Filter based on aggregated value for each dimension member
              Chris Berger

              Karunaker Molugu thanks for you reply. Your solution might tackle the issue of selecting customers with at least 5 period distinct of zero. However, how could I include the constraint of at least 5 consecutive non zero periods?

               

              Regards

              • 4. Re: Filter based on aggregated value for each dimension member
                KK Molugu

                Chris:

                 

                I guess I missed the 'Consecutive' part. This can be achieved using couple more window and table calculations. What I have done is marked it 1 is the factTotal > 0. This will give me each mark if it has a value or not.

                 

                // Check if the > 0

                if zn(sum([factTotal])) > 0 then 1 else 0 end

                 

                Now using that, I want to simulate something like running_sum. I can't use runnng_sum, as it will keep adding even if we miss values in between. To come out of that, I have used previous_value function to keep counting the numbers until the value = 1, When I hit 0, I reset the value 0. Now this will keep counting all consecutive 1's and set to 0 until we have another value.

                 

                first() will just not count the first record to itself.

                 

                // Counting for consecutive

                if first() == 0 then

                    [Has Total > 0 ]

                else

                    if [Has Total > 0 ]  = 1 then

                        [Has Total > 0 ] + previous_value([Has Total > 0 ])

                    else

                        [Has Total > 0 ]

                    end

                end

                 

                Now we need to check the window_max table calculation, so I can filter without filtering the data.

                 

                window_max([Prev value])

                 

                What this does is if there is any record with consecutive sum of >= 5, it will keep it or filter from the view. Hope this helps.

                 

                THis was an interesting one for me and learned few new tricks.

                 

                This can be all created into one formula, but I would like to split it to see what's going on. You can put all in one.

                 

                ..kk