topic Re: Analysis expression with sum (help) in QlikView

Analysis expression with sum (help)

Thu, 11 Oct 2012 09:35:53 GMT

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

Re: Analysis expression with sum (help)

swuehl — Thu, 11 Oct 2012 09:45:01 GMT

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)

Thu, 11 Oct 2012 10:03:34 GMT

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)

swuehl — Thu, 11 Oct 2012 10:12:36 GMT

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)

Thu, 11 Oct 2012 10:16:10 GMT

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

Grtz AbsolutWilot