Cannot get data thru Set analysis

[Anonymous]

Hello,

I encounter some strange in using set analysis in the straight table at Qlik Sense

aggr(NODISTINCT SUM( {$<PODOCNO={'00HMOOCPO1500278-0001'}> } ANQTY), PODOCNO)

[Anonymous]

Try this:

aggr(NODISTINCT SUM( ANQTY), PODOCNO)

[marcus_sommer]

Maybe NODISTINCT caused unexpected results.

- Marcus

[markodonovan]

Hi Janis,

You could try adding an outer aggregation function.

This post by HIC might prove useful:

Pitfalls of the Aggr function

Thanks

Mark

techstuffy.tv

[Anonymous]

Thanks all,

Maybe the filtering issues, aggr(NODISTINCT SUM( {$<PODOCNO={'00HMOOCPO1500278-0001'}> } ANQTY), PODOCNO) is fine and able to get the result.

Actually, my goal is to find outstanding quantity as at a date and based on a status

Since I am not quite familiar with qlik sense, when I translated the above PODOCNO to use date as a filter

Again it fail to display the result, I expect 2, while nothing, is that i am set wrong again?

Or I can use else approach (i.e. rangesum) to achieve it?

Let v_LoadedDate2 = Date(Date#('20151104','YYYYMMDD','YYYY-MM-DD') //this is load script

aggr (NODISTINCT SUM( {$<ANDOCDATE={'<=$(=v_LoadedDate2)'}> } ANQTY), PODOCNO)

Above is the result I set date format in the set analysis

Above is the raw data ,

[marcus_sommer]

There are a few syntax-issues with your variable and expression, try this:

Let v_LoadedDate2 = Date(Date#('20151104','YYYYMMDD'),'YYYY-MM-DD') //this is load script

aggr (NODISTINCT SUM( {$<ANDOCDATE={"<=$(v_LoadedDate2)"}> } ANQTY), PODOCNO)

And your field ANDOCDATE must be formatted like YYYY-MM-DD (within set analysis values and formattings needs to be matched). Very often it's better to use pure numeric fields for each macthing and calculating - extra created within your master-calendar like: num(ANDOCDATE) as ANDOCDATE_NUM - it avoids potentially problems with formattings, it's easier to handle and more performant (at least one parsing step lesser).

Let v_LoadedDate2 = num(Date#('20151104','YYYYMMDD')) // = 42312

aggr (NODISTINCT SUM( {$<ANDOCDATE_NUM ={"<=$(v_LoadedDate2)"}> } ANQTY), PODOCNO)

- Marcus