topic Set Analysis ? in QlikView

Set Analysis ?

Thu, 30 Oct 2014 06:28:05 GMT

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

Re: Set Analysis ?

its_anandrjs — Thu, 30 Oct 2014 06:55:27 GMT

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

Re: Set Analysis ?

vardhancse — Thu, 30 Oct 2014 07:00:56 GMT

Set analysis will work only in front end. Will not work in the back end script.

Re: Set Analysis ?

its_anandrjs — Thu, 30 Oct 2014 07:07:14 GMT

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