topic Re: Sum of the purchases of 1st tickets of the customers in QlikView

Sum of the purchases of 1st tickets of the customers

Mon, 19 Oct 2015 09:57:18 GMT

I Need to calculate : Sum of the purchases of 1st tickets of the customers, sorted out by 1st week of purchase
I write the dimension with no problem at all : =week(aggr(min([Date ticket]),[Code conso]))

for the indicator I make the test :
=sum({<[Date ticket]={"=aggr(min([Date ticket]),[Code conso])"}>}[CA])

But for certain customers the result is false .

the Data is

Consumer ID	Ticket Date	ligne ticket ID	Ticket ID	PRODUCT	Amount	Quantity
357789	06/12/2014	281151170	87488143	93081_01_19/22	11	1
357789	06/12/2014	281151171	87488143	18021_01_12M	45	1
357789	06/12/2014	281151172	87488143	30121_01_12M	69	1
357789	06/12/2014	281151173	87488143	90141_01_46	25	1
357789	30/05/2015	298252889	92124169	25002_45_2A	25,2	1
357789	30/05/2015	298252890	92124169	10022_77_2A	21	1

I want the sum of amount of the 1rst ticket (06/12/2014) of the consumer 357789 : 150

Any other idea?

I put in attached files an example of application and the expected result(profit)

thanks

Re: Sum of the purchases of 1st tickets of the customers

rubenmarin — Mon, 19 Oct 2015 15:33:29 GMT

Hi Magali, can you try?;

For one customer: FirstSortedValue(Sum(Amount), [Ticket ID])

For all customers: Sum(Aggr(FirstSortedValue(Sum(Amount), [Ticket ID]), [Consumer ID]))

Re: Sum of the purchases of 1st tickets of the customers

rubenmarin — Mon, 19 Oct 2015 15:38:44 GMT

And now tested in your qvw, for all customers, can you try?:

Sum(Aggr(If([Ticket ID]=Min(TOTAL <[Consumer ID]> [Ticket ID]), Sum(Amount)), [Consumer ID], [Ticket ID]))

Re: Sum of the purchases of 1st tickets of the customers

Mon, 19 Oct 2015 16:42:53 GMT

thanks a lot for your help Ruben.

My answers :

For one customer: FirstSortedValue(Sum(Amount), [Ticket ID]) => error in expression

For all customers: Sum(Aggr(FirstSortedValue(Sum(Amount), [Ticket ID]), [Consumer ID])) => 0

and the third is OK !

Sum(Aggr(If([Ticket ID]=Min(TOTAL <[Consumer ID]> [Ticket ID]), Sum(Amount)), [Consumer ID], [Ticket ID]))

I understood your expression. I do not know well the Aggregation functions but thanks to you I progressed a lot today. Thank you very much