Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Sum not calculated with set analysis

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

3 Replies
MVP
MVP

Sum not calculated with set analysis

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Sum not calculated with set analysis

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

MVP
MVP

Sum not calculated with set analysis

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser