Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

janiskwok
New Contributor

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)

5 Replies
balrajahlawat
Esteemed Contributor

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

markodonovan
Valued Contributor

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

janiskwok
New Contributor

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?

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

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

data_preview2.png

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

data_preview.png

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:

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