4 Replies Latest reply: Oct 11, 2012 6:16 AM by absolutwilot

# Analysis expression with sum (help)

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)

• ###### Re: Analysis expression with sum (help)

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))

• ###### Re: Analysis expression with sum (help)

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

• ###### Re: Analysis expression with sum (help)

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

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

• ###### Re: Analysis expression with sum (help)

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

Grtz AbsolutWilot