Different aggregate in the same table

JMAROUF — Wed, 07 Sep 2022 09:09:29 GMT

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?

Re: Different aggregate in the same table

Digvijay_Singh — Wed, 07 Sep 2022 03:14:09 GMT

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

Re: Different aggregate in the same table

JMAROUF — Wed, 07 Sep 2022 08:39:22 GMT

Hi @Digvijay_Singh ,

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

Thanks in advance;

topic Re: Different aggregate in the same table in App Development

Different aggregate in the same table

Re: Different aggregate in the same table

Re: Different aggregate in the same table