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

    Sum not calculated with set analysis

    gbrandt

      Hi,

      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.

      sales:
      ZIP_code sales_amount
      012345 100
      987655 150
      333333 300

      ZIP_codes:
      ZIP_all
      012345
      222222
      333333
      888888
      987654

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

      dimension:
      ZIP_all

      formula:
      =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

       

       

       

       



       

       

       

       

      sum(

       

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

        • Sum not calculated with set analysis
          Jonathan Dienst

          Georg

          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.

          Jonathan

            • Sum not calculated with set analysis
              gbrandt

              Jonathan,

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

                • Sum not calculated with set analysis
                  Jonathan Dienst

                  Georg

                  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.

                  Jonathan