Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
If I have the dimension of customer, and the two measures of Sales value and Date.
How in a table grouped at Customer level can I show the minimum date WHERE the sales value is the maximum, ie the same sales value could repeat across dates and I want the earliest that the max has occured.
I have tried to manipulate an AGGR function but to no avail
Is there a way of doing this without changing the load script
For example if i have the raw data below
Customer | Sales | Date |
A | 50 | 10/12/2015 |
A | 100 | 09/12/2015 |
A | 100 | 08/12/2015 |
B | 75 | 10/12/2015 |
B | 100 | 09/12/2015 |
B | 150 | 08/12/2015 |
I want this table chart in my Qlikview app so for customer A, the max sales is 100, which occurs on two days and in this case i want the earlies date.
Customer | MAX(Sales) | Min(Date) |
A | 100 | 08/12/2015 |
B | 150 | 08/12/2015 |
Slightly modifiying Stefan's expression:
FirstSortedValue(Aggr(Value,YearRef,Value),-Aggr( Count*1E9-Value ,YearRef,Value, Count))
Using your sample data:
SET DateFormat ='DD/MM/YYYY';
LOAD * INLINE [
Customer,Sales,Date
A,50,10/12/2015
A,100,09/12/2015
A,100,08/12/2015
B,75,10/12/2015
B,100,09/12/2015
B,150,08/12/2015
];
Customer | Max(Sales) | FirstSortedValue(Aggr(Date,Customer,Date),-Aggr( Sales*1E9-Date,Customer,Date) ) |
---|---|---|
150 | 08/12/2015 | |
A | 100 | 08/12/2015 |
B | 150 | 08/12/2015 |
or
Customer | Max(Sales) | Date(Min(Aggr( If(Max(TOTAL<Customer> Sales) =Sales, Date), Customer, Date))) |
---|---|---|
150 | 08/12/2015 | |
A | 100 | 08/12/2015 |
B | 150 | 08/12/2015 |
Hi
Thanks a lot for your reply - I have looked at these formulas and they work the majority of the time however there are a few occasions when it falls down.
So I probably wasn't totally clear in my initial question, I have attached a set of sample data - what I want to show is the "value" which is flagged by the arrows, currently where there are multiple "values" which are the same the calculation falls down.
Is there a way around this?
I think you just need to add the Count field to the aggr() dimensions
Slightly modifiying Stefan's expression:
FirstSortedValue(Aggr(Value,YearRef,Value),-Aggr( Count*1E9-Value ,YearRef,Value, Count))
Cheers guys
What's the difference to the expression I suggested ?