Sum of just one field in pivot table

[#1]

Hi friends! This is my first time in Qlikview Forum, so I am glad of being in the Qlikview family. I have a question as below:

I have a table of Sales as following:

Branch date Sales newCustomer

1 1/1/10 100,000 2

1 1/2/10 120,000 3

2 1/1/10 200,000 4

2 1/2/10 150,000 1

I want to have a pivot table that shows the total sales of a branch from the begining to a specific date but the number of new customers of that date (No Sum) like this:

Branch date Sales newCustomer

1 1/2/10 220,000 3

2 1/2/10 350,000 1

How can I have this Repot?

[brenner_martina]

Hi, MK_Komaei,

you have to use the function Max(date) with aggr and the if-then-else-condition or set analysis, you can use the expressions even in a variable, p.e.

date: Aggr(Max(date),Branch)

newCustomer: If(date=Max(date),newCustomerNo)

but here is the problem, what if you have more than one new customer a day?

[vgutkovsky]

I would say that your only expression should be for Sales; the rest should be dimensions. Then the Sales expression could be sum(Sales) with accumulation (so that you see the total sum including previous dates).

Regards,

[#4]

Non of these worked!

Please help me if you can.

[#5]

Can you just use this expression in Sales expression. I think it's will shows the sum sale but not change the value of other dimension.

Sum(TOTAL <Branch> Sales)

Hope this help.

[vgutkovsky]

See attached QVW for an example of the solution.

Regards,