topic Can I use a column in a Sum() expression? in QlikView

Can I use a column in a Sum() expression?

Wed, 13 Aug 2014 09:18:33 GMT

Hi all,

I would like to use the a column in a Sum() expression.

To keep it simple imagine a chart that has a dimension = customer number and 2 expressions for summing the sales and the cost as follows . .

Customer No Sales Cost
123 100 80

456 200 140

789 50 40

The label for Sales = Sales and the expression for Sales = ([Sales LC - Net Sales Amount])
The label for Cost = Cost and the expression for Cost = ([Sales Extended Item Cost])

I wanted to add a margin. The label for Margin = Margin and the expression for Margin = Column (1) - Column (2)

This works fine. As does this expression = Sales - Cost

So, I can address the column by it's number or it's label.

Out of interest, to see if I could retrieve the customer number, I created another column. The label for it = CusNo and the expression for it = [Sales Customer Number]. It worked. The chart now looks like this . . .

Customer No Sales Cost Margin CusNo
123 100 80 20 123

456 200 140 60 456

789 50 40 10 789

So far, everything works perfectly. Now, I'd like to use the customer number in a Sum() expression but can't seem to get it to work.

I've tried several different ways and am basically trying to say the following . . .

Sum({1<[Budget Budget Customer Number] = {Column(4)}>} Total [Budget Budget Value])

Can I address the column this way? I've also tried using the column label (CusNo) but with no joy. I've dabbled with dollar sign expansion but can't get it to work. Either I can't use the column in the Sum() expression or I can't find the correct syntax for the dollar sign expansion.

Regards,

Geoff.

Re: Can I use a column in a Sum() expression?

tresB — Wed, 13 Aug 2014 09:27:20 GMT

This would not work as the set analysis part would try to filter the data before it enters the chart row-wise calculation. However, you may probably try with IF like:

Sum(total If([Budget Budget Customer Number] = Column(4), [Budget Budget Value]) )

Re: Can I use a column in a Sum() expression?

jagan — Wed, 13 Aug 2014 09:29:41 GMT

Hi,

Sum({1<[Budget Budget Customer Number] = {Column(4)}>} Total [Budget Budget Value] ) this expression won't work in Qlikview, since the expression is evaluated at chart level not row level, instead you can try like this

=Sum({1<[Budget Budget Customer Number] = P([Sales Customer Number])>} Total [Budget Budget Value])

Hope it helps you.

Regards,

Jagan.

Re: Can I use a column in a Sum() expression?

Wed, 13 Aug 2014 09:48:21 GMT

Hi Jagan,

can you point me to an explanation of the P() and E() indirect commands please so I can understand this further?

What you suggested seems to work if a particular customer is selected. However, if a customer isn't selected then it sums the budget for every customer.

What I really need to be able to do is call a macro for each row that is written, pass to it the customer number, and then run a script to calculate the budget based on that particular customer (and also other possible selections (year, quarter, period, sales family etc.)).

I asked the following but it seems like what I want to do can't be done this way . . .

http://community.qlik.com/thread/128013

Regards,

Geoff.