
1. Re: Filter based on aggregated value for each dimension member
Michael Carper Dec 15, 2014 12:59 PM (in response to Chris Berger)Try this table calculation on for size. The downside if that you have to handcode the row offset (ie it looks at periods 04, or 15, or 26). 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

customer lifecycle.twbx 1.0 MB


2. Re: Filter based on aggregated value for each dimension member
KK Molugu Dec 15, 2014 2:29 PM (in response to 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)
With that calculation, as you said ,it would be cumbersome.
..kk

3. Re: Filter based on aggregated value for each dimension member
Chris Berger Dec 17, 2014 6:59 AM (in response to KK Molugu)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 Dec 17, 2014 8:40 AM (in response to Chris Berger)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