5 Replies Latest reply: Apr 2, 2012 9:05 AM by Mathias Vanden Auweele RSS

    How to get value, depending on unique value of other fields

    Mathias Vanden Auweele

      Hello all!

       

      I have a problem which I have simplified below. I have the following fields in a data table:

       

      SALES
      ENTRY_ID
      

       

      Due to the necesity to built a star sheme as data model, I needed to join this table with some others resulting to a final table which looks like this:

       

      SALES
      ENTRY_ID
      EXTRA_FIELD
      

       

      I need to sum the SALES but only for thoose records where the entry_ID is distinct. In my first data model I would have done:

       

      sum(SALES)
      

       

      Now I will do:

       

       

      sum(aggr(only(SALES),ENTRY_ID))
      

       

       

      (is that right?)

       

      Now the problem is, I need to use this function in a chart with as dimension KPI_ID (a fourth value, independent on the first three values). To avoid the problem stated here (http://community.qlik.com/message/166081#166081), I will need to add 'nodistinct' to my expression:

       

      sum(aggr(nodistinct only(SALES),ENTRY_ID))
      

       

      However, now the result gives me the same as if I would use sum(SALES), all the SALES are included, even thoose that have the same ENTRY_ID!! How can I solve this problem?

       

      Please also see the attached qvw.

       

      Thank you very much!