topic Convert this sum aggr if expression to sum with set analysis aggr in QlikView

Convert this sum aggr if expression to sum with set analysis aggr

rpavan17 — Sat, 16 Nov 2024 19:16:46 GMT

Hi,

Can anyone help to convert this formula to set analysis expression?

Date_Clearing, Date_Posting and Month_End are in 'DD-MM-YYYY' format.

sum(aggr(if(sum(if(((IsNull(Date_Cl) AND Date_Pos <= Month_End ) OR (Date_Pos <= Month_End AND Date_Cl > Month_End)) and SAP_TC='FBL1N' and trim(SP_GL)='' ,Amount_LOC,0))>0,

sum(if(((IsNull(Date_Cl) AND Date_Pos <= Month_End ) OR (Date_Pos <= Month_End AND Date_Cl > Month_End)) and SAP_TC='FBL1N'and trim(SP_GL)='',

Amount_LOC,0)),0),VendorCC,Month_End))

Thanks

Re: Convert this sum aggr if expression to sum with set analysis aggr

alex00321 — Tue, 21 Jan 2020 08:00:46 GMT

Hi can you provide sample data maybe and also expected result? Thanks!

Re: Convert this sum aggr if expression to sum with set analysis aggr

Brett_Bleess — Thu, 13 Feb 2020 19:37:12 GMT

Have a look at the following Design Blog post, believe it may be of some help on this issue:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Regards,
Brett