5 Replies Latest reply: Mar 21, 2011 1:14 PM by Sravan Puppala RSS

    Aggr and Setanalysis

    Sravan Puppala

      Hi Guys,


      After struggling for sometime and could not found the way to write the Expression with setanalysis and aggr function, I am posting this here in Forum. May be someone can help me with the Expression described in the Excel sheet attched

       

      [View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.00.49.52/Problem-with-aggr.xls]

       

      Regards

      Sravan

        • Aggr and Setanalysis
          Neil Miller

          This one is going to be difficult to troubleshoot with just that Excel sheet. Complicated Set Analysis/Aggr functions are very dependent on the data structure, so troubleshooting expressions without sample data is difficult.

          Looking at what you have, I think you have too many Aggr functions. All of your Aggrs are on the same field MachineDefinitionId. You should be able to get rid of at least one.

          I would try:

          =sum(
          aggr(
          (
          ([set (pcs/sec)]*
          sum({<ProductCategory ={'G'} >} ActivityDuration)) -
          sum(Output)
          ) * StandardValueAddedTime ,
          MachineDefinitionId)
          )


          There are also two fields which are not within aggregate functions. Since Aggr is something like a Group By in SQL, I think you may need all other fields to be within aggregate functions. If there is only one value per MachineDefinitionId, you could use Only (otherwise, Max may be the best choice.

          Like:

          =sum(
          aggr(
          (
          (Only([set (pcs/sec)]) *
          sum({<ProductCategory ={'G'} >} ActivityDuration)) -
          sum(Output)
          ) * Only(StandardValueAddedTime) ,
          MachineDefinitionId)
          )


          Those are just some ideas based on your sample. What is your current expression returning? All Nulls?