Help needed for Nested Aggregation

[rajaiqliksense]

Dear Friends!

I've a scenario to calculate closing stock. please check expression below which is working while selecting company code separately Eg. company_code ='X' (Filter pane input).

 (sum({<GNAME={"Closing Stock"},VOUCHERDATE={"$(=date(Max(clmax),'YYYY-MM-DD'))"}>} actual_stock)/100000)

when multiple company selected {''x,'y','z'}, this expression return the wrong figure, .

Here clmax is company_code wise maximum date.

So shortly, need to sum closing stock value (stock on maximum date) of all company.

Tried aggr expression like below, but no result. 

SUM(AGGR((sum({<GNAME={"Closing Stock"},VOUCHERDATE={"$(=date(Max(clmax),'YYYY-MM-DD'))"}>}VL)/100000), COMPANY_CODE))

Really would be grateful, if any idea from your side.

[Anil_Babu_Samineni]

First check this using filter A, B & C from company_code?

sum({<VOUCHERDATE={"$(=date(Max(clmax),'YYYY-MM-DD'))"}>} actual_stock)/100000

Or using this?

 (sum({<company_code = P(company_code), GNAME={"Closing Stock"},VOUCHERDATE={"$(=date(Max(clmax),'YYYY-MM-DD'))"}>} actual_stock)/100000)

[rajaiqliksense]

Hello Friend!

Thanks for timely reply.

I'm so grateful to you friend.

But you shared one expression which would work, when company code has same Closing date.

Actually, if company wise closing date (CLMAX) varies like  a->"31-12-2018" and b->"30-12-2018", then only problem comes.

if a and c has same date, then able to sum both of values.

Please kindly help

Thanks

[marcus_sommer]

You could try it with:

sum({<GNAME={"Closing Stock"}, COMPANY_CODE= {"=VOUCHERDATE=Max(clmax)"}>} actual_stock)

or maybe

if(VOUCHERDATE=Max(clmax),sum({<GNAME={"Closing Stock"}>} actual_stock))

- Marcus