Max create date aggr

[krmvacar]

Hi guys ,

I want to 

SUM({<(max([date]),[DOCNO]={'31'})>} [.GRANDTOTAL]) ,its not working.

Can you help me?

[GaryGiles]

I think what you might be looking for is:

SUM({<[date]={'$(=max([date]))'},[DOCNO]={'31'}>} [.GRANDTOTAL]) 

[krmvacar]

Thank you for answer. ı try But is not working. Answer give me zero:( 

SUM({<[CREATEDAT]={'$(=max([CREATEDAT]))'},[DOCNO]={'31'}>} [GRANDTOTAL])

[MayilVahanan]

Hi @krmvacar 

Try like below

SUM({<[CREATEDAT]={"$(=Date(max([CREATEDAT]),'DD/MM/YYYY'))"},[DOCNO]={'31'}>} [GRANDTOTAL])

Change the DD/MM/YYYY date format to ur Createdat date format.

[krmvacar]

Hİ MayilVahanan,

I try but not working.

My data;

Now ı want to show,

and ı want to result=304.

Thank you so much

[MayilVahanan]

HI @krmvacar 

So, you don't want max date for entire DocNo, its based on GrandTotal?
Your source has single record per date, docno & GrandTotal? or multiple singles available per date, docno?

[krmvacar]

Hi @MayilVahanan 

Your source has single record per date, docno & GrandTotal. 

[MayilVahanan · Accepted solution]

Hi @krmvacar 

If you have one record per combination, try like below

Temp:
LOAD * INLINE [
CREATEDATE, DOCNO, GRANDTOTAL
12.01.2021, 31, 148
15.01.2021, 31, 148
18.01.2021, 31, 156
];

Load *, if(GRANDTOTAL <> Peek('GRANDTOTAL'), 1, 0) as flag
Resident Temp Order By GRANDTOTAL, DOCNO, CREATEDATE desc;

DROP Table Temp;

EXIT SCRIPT;

Exp: SUM({<flag={1}>} [GRANDTOTAL])

[krmvacar]

Thank you so much

its working 😀