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

    Cannot get data thru Set analysis

    Janis Kwok

      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
          balraj ahlawat

          Try this:


          aggr(NODISTINCT SUM( ANQTY), PODOCNO)

          • Re: Cannot get data thru Set analysis
            Marcus Sommer

            Maybe NODISTINCT caused unexpected results.

             

            - Marcus

            • Re: Cannot get data thru Set analysis
              Mark O'Donovan

              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
                  Janis Kwok

                  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
                      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