unable to filter with Aggr formula for dimension

43918084 — Tue, 30 Nov 2021 16:25:32 GMT

I have created below formula for a dimension but I found that I cannot filter by specific item. For example, if I want to filter only 'Nomvt' , it will show all the below values ('New', 'Repay', 'FL', 'Nomvt, etc)

May I know if there is anyway to allow filter by specific item in the formula? Many thanks in advance.

=aggr( if((Sum(t0_GCV_HKD000)=0 and sum(t1_GCV_HKD000)<>0),'New',
if((Sum(t0_GCV_HKD000)<>0 and sum(t1_GCV_HKD000)=0),'Repay',

if(right(t1_stage,1)>right(t0_stage,1),'Dn_'&right(t1_stage,1)&' fm '&right(t0_stage,1),
if(right(t1_stage,1)<right(t0_stage,1),'Up_'&right(t1_stage,1)&' fm '&right(t0_stage,1),
if((Sum(t0_GCV_HKD000)<>0 and sum(t1_GCV_HKD000)<>0 and sum(t1_GCV_HKD000)-sum(t0_GCV_HKD000)>0),'FL',
if((Sum(t0_GCV_HKD000)<>0 and sum(t1_GCV_HKD000)<>0 and sum(t1_GCV_HKD000)-sum(t0_GCV_HKD000)<0),'Repay',

if(fabs((Sum(t1_GCV_HKD000)- sum(t0_GCV_HKD000))=0),'Nomvt',
' '))))))),[Ip Lcl Ref Cde Ith],t1_stage,t0_stage)

Re: unable to filter with Aggr formula for dimension

rubenmarin — Sat, 02 Oct 2021 07:07:40 GMT

Hi, you can only apply selections in loaded fields, values have to be laoded while loading to make it selectables.

If possible the best option is to make a similar calculation in script to have Nomvt, Repay... as values in a field. This is possible if the values assigned to each combination of [Ip Lcl Ref Cde Ith],t1_stage,t0_stag is not affected by selections, or if there is a way to workaround selections to have values precalculated.

Another option could be to have different butons, or an island table with the values preloaded and change the expression to show the values that matches the values selected in that island table. the possibility of this depens on what table or chart do you want to affect this filters, because expressions would be more complex.

topic Re: unable to filter with Aggr formula for dimension in App Development

unable to filter with Aggr formula for dimension

Re: unable to filter with Aggr formula for dimension