Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I encounter some strange in using set analysis in the straight table at Qlik Sense
aggr(NODISTINCT SUM( {$<PODOCNO={'00HMOOCPO1500278-0001'}> } ANQTY), PODOCNO)
Try this:
aggr(NODISTINCT SUM( ANQTY), PODOCNO)
Maybe NODISTINCT caused unexpected results.
- Marcus
Hi Janis,
You could try adding an outer aggregation function.
This post by HIC might prove useful:
Thanks
Mark
techstuffy.tv
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 ,
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