Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I am trying to create a filter based on the below logic but getting error of Invalid Dimension.
Can anyone please sort this out for me.
Filter logic:
=if(Sum(if((D_MONTH<vMonth and D_YEAR=Year(Now())) or (D_MONTH>=vMonth and D_YEAR=Year(Now())-1), D_AMOUNT_IN_EUR_12MONTHS_Parent))=0,'Prospect')
Here vMonth is variable whose value is Let vMonth = num(Month(Today()))
Also value of D_Month and D_YEAR is numerical
Table _Name:
load
D_MONTH,
D_YEAR,
if(Sum(if((D_MONTH<vMonth and D_YEAR=Year(Now())) or (D_MONTH>=vMonth and D_YEAR=Year(Now())-1), D_AMOUNT_IN_EUR_12MONTHS_Parent))=0,'Prospect') as IsProspect
resident Main_table
Group By D_YEAR,D_MONTH;
You may consider moving this to the load script and creating this as a field in the table.
For example,
your_table:
load
D_MONTH,
D_YEAR,
if(Sum(if((D_MONTH<vMonth and D_YEAR=Year(Now())) or (D_MONTH>=vMonth and D_YEAR=Year(Now())-1), D_AMOUNT_IN_EUR_12MONTHS_Parent))=0,'Prospect') as IsProspect
resident your_original_table
group by D_MONTH, D_YEAR;
This could work depending on your data structure. Could you provide a sample of what the raw data looks like?
Table _Name:
load
D_MONTH,
D_YEAR,
if(Sum(if((D_MONTH<vMonth and D_YEAR=Year(Now())) or (D_MONTH>=vMonth and D_YEAR=Year(Now())-1), D_AMOUNT_IN_EUR_12MONTHS_Parent))=0,'Prospect') as IsProspect
resident Main_table
Group By D_YEAR,D_MONTH;