problem to evaluate column(1) within aggr expression

[JMAROUF]

Hi,

Here is a sample to represent my data (I m in a complicated star schema):

LIEN:
LOAD * INLINE
[
id_l,pole,region
l1,regional,sud
l2,smallcapp,est
];

INSTR:
LOAD * INLINE
[
id_instr,instrument
instr1,a
instr2,b
instr3,c
];

PAR:
LOAD * INLINE
[
id_par,nom_par
par1,cita
par2,keen
];

INSVT:
LOAD * INLINE
[
id_invst,nom_invs
invst1,bpi
invst2,ffi
];

INVESTISSEMENT:

LOAD * INLINE
[
id_l,id_invst,id_par,id_instr,date,investissement,flag_invest
l1,invst1,par1,instr1,01/01/2024,10,invest
l1,invst1,par1,instr1,31/01/2024,11,reinvest
l1,invst1,par1,instr2,01/04/2024,15,invest
l2,invst2,par2,instr3,15/01/2024,20,invest
l2,invst2,par2,instr3,31/01/2024,21,reinvest
l2,invst2,par2,instr3,31/01/2024,21,reinvestissement
];

I'm my goal is to show result based on column(1) value within an aggr function;

this expression doesn't work and I don't understand why ?

=>  =sum(aggr(if(Column(1)>0,count(distinct id_par),0),region,nom_par))

I have used this instead whitch gives the desired result, however in my real expression I a huge complicated formula with many lines of code.

alternative=> =sum(aggr(if(sum({<date={"<=$(=v_fin)"},fait={'investissement'},flag_invest={'reinvest'}>} investissement)
>0,count(distinct id_par),0),region,nom_par))

Can any one explain to me why column(1) doesn't work in my example please?

Can 

[Kushal_Chawda · Accepted solution]

@JMAROUF  

Column() functions returns the value found in the column corresponding to ColumnNo in a straight table, disregarding dimensions. Hence, aggr function on top of it won't work because it doesn't evaluate the expression against dimension, but simply looks at already evaluated value against that dimension.

[JMAROUF]

@Kushal_Chawda  thank you for the explanation, is there any alternative instead to avoir writing all the expression?

[Kushal_Chawda · Accepted solution]

@JMAROUF  I don't think there is any alternative unless you want to create a Flags in script to simplify expression