Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi All,
I have created front End Expression in Set Analysis but I need to create in Back End ..
please help ?
if(DR_CR='D',
sum(OPENING)+
sum({<TD_DOC_DT={"<=$(=max(TD_DOC_DT))"},TD_DOC_DT_Year=,TD_DOC_DT_Month=,TD_DOC_DRCR_FLAG={'D'}>}TD_DOC_AMT)-
sum({<TD_DOC_DT={"<=$(=max(TD_DOC_DT))"},TD_DOC_DT_Year=,TD_DOC_DT_Month=,TD_DOC_DRCR_FLAG={'C'}>}TD_DOC_AMT),
sum({<TD_DOC_DT={"<=$(=max(TD_DOC_DT))"},TD_DOC_DT_Year=,TD_DOC_DT_Month=,TD_DOC_DRCR_FLAG={'D'}>}TD_DOC_AMT)-
sum({<TD_DOC_DT={"<=$(=max(TD_DOC_DT))"},TD_DOC_DT_Year=,TD_DOC_DT_Month=,TD_DOC_DRCR_FLAG={'C'}>}TD_DOC_AMT)-sum(OPENING))
Thanks,
Helen
Try this way
LET vMAX = max(TD_DOC_DT);
Load
DR_CR,
TD_DOC_DRCR_FLAG,
if(DR_CR='D',
sum(OPENING) +
sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT ) ) -
sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT ) ),
sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'D', TD_DOC_AMT) )-
sum( If( TD_DOC_DT <= $(vMAX) and TD_DOC_DRCR_FLAG = 'C', TD_DOC_AMT) )-
sum(OPENING)) as SumField
Resident Source
Group BY DR_CR,TD_DOC_DRCR_FLAG;//According to you use the fields
and rest of the SET filter expression in the front end
Sum({<TD_DOC_DT_Year=,TD_DOC_DT_Month=> } SumField)
Note:- SET expression not able to use in the load script but you can use only the SUM aggregate function and then rest of the SET expression in the front end.
Regards
Anand
Set analysis will work only in front end. Will not work in the back end script.
Hi,
Helen SET expression not worked in the load script you have to use only SUM function in the load and then rest of the SET expression in the front end.
Regards
Anand