Need to extract customer ids of the maximum transaction defined as a measure

[bogdan90]

Hey everyone, 

I am really new to qlik and lately I have been struggling for a while to extract information from the data.

So here's the problem, I have a source table of transaction per customer which contains transaction id, transaction date, customer id, transaction amount, bonus, store location and a dimension table which contains customer related information.

I have defined a measure called Maximum transaction which seem to link correctly with store location (see pic 1), however when I add a customer id dimension to the table it seem to return maximum amount for all customers that made purchase at the given location (see pic 2):

pic 1

Store id	Largest transaction in october 2019	Bonus saved
15	100000	10

pic 2

Store id	Largest transaction in october 2019	Bonus saved	Customer id
15	100000	10	23
15	90000	9	24
15	87000	12	15
15	45000	8	12
15	14000	7	72
15	12500	6	45

The measure for maximum amount is defined with the following setAnalysis expression

Max (
{
$<[transaction_calender_year_number]       =  {"$(=var_selected_year_number)"},
     [transaction_calender_month_number]  =  {"$(=var_selected_month_number)"}>
}
[transaction_amount])

where I basically define that I need maximum transaction for a certain year and month. As far as I understand in order to link it correctly with customer id and customer related information I need to use aggregation function, but I struggle to implement it correctly.

Any help is highly appreciated

[JordyWegman]

Hi Bogdan,

What were you expecting in Pic 2? 

Jordy

Climber

[bogdan90]

Hey Jordy, I was expecting only the first row

[JordyWegman]

In that case, try this in your customer dimension:

=IF(Transaction = Max(Total Transaction ),Customer,null())

Your dimension will split it, but with this formula it will only get the one with the largest  transaction.

Jordy

Climber