Different aggregate in the same table

[JMAROUF]

Hi,

I want to check the state of a link within a table based on the date written by the user, the formula is based in Link aggregation, wheras I have to show the instrument granulariti within the same table, a link is out where the sum QT of all  it's instruments is 0,   here is my data:

FACT:
LOAD * INLINE [
LINK,INSTRUMENT,QT,%DATE
1,INST1,5,31/12/2022
1,INST2,0,30/06/2022
2,INST1,0,31/12/2022
2,INST3,0,30/06/2022
];

PS: we are in a star schema, the sample above is just for the fact which is related to other dimensions (LINK,INSTRUMENT...)

There is the two formulas I use:

QT: Sum(aggr(FirstSortedValue(DISTINCT TOTAL<LINK,INSTRUMENT>{<[%DATE]={"<=$(=vDate)"}>} QT,-[%DATE]),LINK,INSTRUMENT))

STATE:  =IF( Sum(aggr(FirstSortedValue(DISTINCT TOTAL<LINK>{<[%DATE]={"<=$(=vDate)"}>} QT,-[%DATE])
,LINK))=0,'OUT','IN')

Here is the output:

Desired:

because the sum QT of link 1 before 2022/12/31 is 5 not 0, so it's state is IN

Any help please?

[Digvijay_Singh]

Why INST3, LINK1 is showing desired output 'OUT'? That as well falls before 2022/12/31!

[JMAROUF]

Hi @Digvijay_Singh ,

I have corrected the desired outpu and DATAt, I meant LINK2.

Thanks in advance;