Analysis expression with sum (help)

[#1]

Hi,

Im relative new to Qlikview, so it can be a stupid question

Situation:

I got a straight table with the dimension "Klant" (customer in english), also i got an expression for the sum of his sales. but the problem now is i want a ratio of that customer with his country. So in fact sum(sales customer) / sum(sales country).

Each sales record contains a customerNumber, Country number and a saleprice

Problem:

I cant get the total of the sales from country, because the dimension is klant (customer) the sales are grouped by customer. im trying to get it by analysis expression but i cant get the right results

the expression i have is:

=sum(total {1<landnummer= {$(=only({$}landnummer))}>} omzet)

translation:

     landnummer = countrynumber,

     omzet  = salesprice

When i split up the two expressions they both works fine:

=sum(total {1<landnummer= {1}>} omzet) // returns all sales record from country 1

=only({$} landnummer)  // returns the country nummber of the record

I hope u got all the needed information and sorry for my english (not a native english speaker)

Thanks in advance

[swuehl]

The set expression is only evaluated once per chart, so it will not take the current landnummer of the current dimension value into account.

Try maybe something like this:

=sum(omzet) / sum(aggr( sum(total<landnummer> omzet), landnummer, klant))

[#3]

This is half  correct. It gives the right value back WHEN dont have a selection. The total value become the total for that customer. The problem here is the sum(totalsales country) must be indepentend on the customernumber and may only be dependend on the countrynumber

[swuehl · Accepted solution]

Right, so let's ignore selection in Klant for the total calculation:

=sum(omzet) / sum(aggr( sum({1} total<landnummer> omzet), landnummer, klant))

[#5]

Thats it! Thanks very much was breaking my nut over it all morning

Grtz AbsolutWilot