Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Bogdan,
What were you expecting in Pic 2?
Jordy
Climber
Hey Jordy, I was expecting only the first row
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