5 Replies Latest reply: Nov 9, 2015 2:52 AM by Marcus Sommer

# Cannot get data thru Set analysis

Hello,

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

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

• ###### Re: Cannot get data thru Set analysis

Try this:

aggr(NODISTINCT SUM( ANQTY), PODOCNO)

• ###### Re: Cannot get data thru Set analysis

Maybe NODISTINCT caused unexpected results.

- Marcus

• ###### Re: Cannot get data thru Set analysis

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

• ###### Re: Cannot get data thru Set analysis

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?

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 ,

• ###### Re: Cannot get data thru Set analysis

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

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