Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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