3 Replies Latest reply: Aug 10, 2010 5:23 PM by Jonathan Dienst RSS

    Sum not calculated with set analysis



      I try to make a result list that counts sales amounts for locations selected in another list by zip code.
      the following is a simplified setup. That is why it might look a bit stupid--but it shows the problem I deal with.

      ZIP_code sales_amount
      012345 100
      987655 150
      333333 300


      Now I want to have a diagramm/table with ...


      =sum({< ZIP_code = {"=ZIP_all <= ZIP_code"} >} sales_amount)

      If I select "22222" and "333333" it *should* return:

      222222 100
      333333 400

      What it does return though is:

      222222 0
      333333 0

      Obviously it that does not work, if more than one ZIP_all is selected from ZIP_codes. John wrote in another thread "A set is only build once per chart, not once per row". So I see that the set does not make sense if ZIP_all is a selection ov values.

      But how else can I achieve this? How can I sum up values (one per line) that have an "condition".

      I also tried











      if(ZIP_ALL <= ZIP_code), sales_amount)




      But that gives a "0" result as well. I just don't see to make a "list of seperate sums based on a condition".

      Best regards,

        • Sum not calculated with set analysis
          Jonathan Dienst


          I am not quite sure what you are trying to do, but I don't think that you need a set expression here, just sum(sales_amount) as your graph is already dimensioned by Zip_All.


            • Sum not calculated with set analysis


              thanks for picking up my issue. Please check again. I need to sum up elements <= ZIP_all, which results in a collecion of items. If it were =ZIP_all it would surely work like that.

              I now did it with two dimensions ZIP_all and ZIP_codes and used as a formula:










              sum(if(ZIP_ALL <= ZIP_code), sales_amount)









              And then pivoted it with ZIP_all. That seems to work. But I feel still unsure if it really does and if this is the best solution. I think my problem is similar to this one: http://community.qlik.com/forums/p/32278/124939.aspx

              Best regards,

                • Sum not calculated with set analysis
                  Jonathan Dienst


                  As John Witherspoon says in that thread, set analysis is performed once for the whole model, not by chart dimension, so you can rule that out as the solution to your problem.

                  That leaves you with the sum-if approach. Without seeing your model, I can't be sure that what you outlined is correct, but if not, you seem to me to be on the right track.

                  If you would like to post your QVW file, I would be happy to have a look at it and possibly make a better suggestion.