Calculate using 'only'/'distinct'

[kennethand]

I have a set of data with respondents choosen a city the like to visit.

Every respondents has an ID and a weight which I'mt using for calculation.

The data file look like this ( and the results I like to produce in Qlik at the bottom):

I like to measure how in percent as choosen City A, City B ect. - but only out the ID's who has choosen a city.

I have done the calculation at the bottom: 2.9 hos choosen City A (the wieght added up from ID 1, 2 and 3). Using this Expression I can calculate the number in qlik sense:

=Sum(Aggr(Only([weight]), Uniq_ID))

So far so good. But how do I calculate the total weight from the ID who has choosen a City (when that is done I can measure the % as I like). Id 5 will not qualify as he hasn't choosed any city. Therefor the total should be equal 3.4.

But how do I make the right expression?

[petter]

You don't need to use Aggr() at all - have look at this:

If your city has a null value instead of an empty string you have to replace =Len(Cities)>0 with =Not(IsNull(Cities))

[kennethand]

Thanks for the answer which was very usefull.

But as I see it it will only make the calculation for City A.

I need an expression working for City B, City C ect. (all cities actually).

How do I  that ?

[petter]

Like this:

[kennethand]

Hi Petter,

Thanks - this is still pretty new to my. I'm not sure how to deal with you're solution.

I makes a variable called v3 (egual 100%) ?

And how to deal with the expression:

sum(distinct{<Uniq_ID={"=Len(MaxString(Kommune))>0"}>}weight)  /

sum(total distinct{<Uniq_ID={"=Len(MaxString(Kommune))>0"}>}weight)

[petter · Accepted solution]

You don't need to use a variable at all. It is only used as a placeholder for the expression so the expression can be used in more than one sheet object/chart without having to rewrite the entire expression.

You can just as well add the entire expression into the expression property of the table:

Sum( DISTINCT {<Uniq_ID={"=Len(MaxString(Kommune))>0"}>} weight )  / Sum(TOTAL DISTINCT {<Uniq_ID={"=Len(MaxString(Kommune))>0"}>} weight )

The 100% is just a sum of all the row expression results which naturally will add up to 100% as it should.