topic Sum not calculated with set analysis in QlikView

Sum not calculated with set analysis

Tue, 10 Aug 2010 09:10:30 GMT

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

jonathandienst — Tue, 10 Aug 2010 11:43:01 GMT

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

Tue, 10 Aug 2010 12:58:48 GMT

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

jonathandienst — Tue, 10 Aug 2010 21:23:25 GMT

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