topic Re: Manipulating data in QlikView

Manipulating data

Mon, 11 Mar 2013 01:14:34 GMT

Hi everyone,

I'm new with Qlikview and i have some problems, specialy with expressions.

I have Two files like that :

file1

Product	Price
1	17
2	19
3	21
4	23

file2

Client	Product
Client 1	1
Client 1	2
Client 2	1
Client 1	3

I want to calculate How much one client spent and display it, so i want to declare a new variable "depense" wich be like that sum(count(products)*price).

I want to know how to do this.

thanks for your answer

Re: Manipulating data

Mon, 11 Mar 2013 03:39:46 GMT

Hi,

try this expression^^

sum(aggr(count(Product)*Price,Product,Client))

Regards,

Alex

Re: Manipulating data

Gysbert_Wassenaar — Mon, 11 Mar 2013 09:32:07 GMT

It's not clear to me if Product is a count or a product id. Depending on what's what there are different solutions. See attached qvw.

Re: Manipulating data

Mon, 11 Mar 2013 19:52:43 GMT

Thanks for your answers,

it helped me, but there is still confusion.

If for exemple Client 1 bought the same product in an another day :

Client	Product	Date
Client 1	1	1/1/2013
Client 1	2	2/1/2013
Client 2	1	3/1/2013
Client 1	1	4/1/2013

If i do the sum, Qlikview will eliminate a double. how to fix that?
I want to notice that Product is an ID.

Best Regards,

Re: Manipulating data

Gysbert_Wassenaar — Tue, 12 Mar 2013 10:09:43 GMT

You'll need to change the expression to sum(aggr(Price*count(Client),Product,Client))