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

# Filter based on aggregated value for each dimension member

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. TWBX file attached:

cheers!

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

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

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) Michael Carper

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

..kk

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

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

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