0 Replies Latest reply: Aug 20, 2018 10:33 PM by Savita Khyadi RSS

    issue with Aggr function in Straight table

    Savita Khyadi

      Hi All,

      I have a straight table, and I am writing below expression. but only first part of the expression where i used distinct gives the correct result, whereas in 2nd part its not showing correct value, i need to write it into a different fashion but am not able to.

      PS: Am new to Qlik

       

      num((sum(aggr(distinct max({$<Flag = {'Opportunity'},[Last Approved OBR Flag]={'Y'},[Approval Record Type]={'Opportunity Business Review'},[Sales Stage]-={'01 - Understand Customer','02 - Validate Opportunity',

      '03 - Qualify the Opportunity',

      '04A - Develop Solution',

      '04B - Propose Solution',

      '05 - Negotiate & Close',

      'HPE Not Pursued',

      'Lost',

      'Error',

      'Unknown'},[PN LSR Proportion$]-={} >}[PN LSR Proportion$]),[HPE Opportunity Id],[Approval ID],GBU,[TS Approval GBU]))

      /

      (sum(aggr(distinct max({$<Flag = {'Opportunity'},[Last Approved OBR Flag]={'Y'},[Approval Record Type]={'Opportunity Business Review'},[Sales Stage] = {'06 - Won, Deploy & Expand','HPE Not Pursued','Lost'}

      ,[PN LSR Proportion$]-={} >}[PN LSR Proportion$]),[HPE Opportunity Id],[Approval ID],GBU,[TS Approval GBU])))),'###0.0%') ,

       

       

      num((sum(aggr(nodistinct max({$<Flag = {'Opportunity'},[Last Approved OBR Flag]={'Y'},[Approval Record Type]={'Opportunity Business Review'},[Sales Stage]-={'01 - Understand Customer','02 - Validate Opportunity',

      '03 - Qualify the Opportunity',

      '04A - Develop Solution',

      '04B - Propose Solution',

      '05 - Negotiate & Close',

      'HPE Not Pursued',

      '06 - Won, Deploy & Expand',

      'Error',

      'Unknown'},[PN LSR Proportion$]-={} >}[PN LSR Proportion$]),[HPE Opportunity Id],[Approval ID],GBU,[TS Approval GBU]))

      /

      (sum(aggr(nodistinct max({$<Flag = {'Opportunity'},[Last Approved OBR Flag]={'Y'},[Approval Record Type]={'Opportunity Business Review'},[Sales Stage] = {'06 - Won, Deploy & Expand','HPE Not Pursued','Lost'}

      ,[PN LSR Proportion$]-={} >}[PN LSR Proportion$]),[HPE Opportunity Id],[Approval ID],GBU,[TS Approval GBU])))),'###0.0%')