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

[#1]

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.

[tresesco]

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

[jagan]

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.

[#4]

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.