Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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.