Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
op 5 of their clients based on the number of sales. Simple enough so far.
Where it gets tricky is when I try to evaluate the total TOP 5 based on each TOP 5 of each day.
Here's an example to make it easy to understand:
On the 18th of Feb, I've got this 5 guys:
However on the 16th I've got a different list. In this case ID 6857 appeared
What I want therefore is a list of this 6 guys and their total over the month:
I'm succeeding with the following dirty expression, which validates the idea however I'm struggling at trying it into a clean set analysis for each day in the current month.
If(rank(Sum({$<order_date_add = {'18/02/2014'} >}Counter))<=5
Or rank(Sum({$<order_date_add = {'16/02/2014'} >}Counter))<=5
,Sum({$<order_date_add_MonthName = {'$(vMonthYesterday)'},order_date_add = >}Counter))
I assume I shall use an Aggregation such as the following one, which unfortunately doesn't work:
rank(Aggr(If(rank(Sum({$<order_date_add_MonthName = {'$(vMonthYesterday)'} >}Counter))<5,1),order_date_add))
Thanks a lot !
Jerome Couzy
Use below as an aggregated dimension
=Aggr(IF(
Rank(SUM({<order_date_add = {'18/02/2014'}>}Counter))<=5
or
Rank(SUM({<order_date_add = {'16/02/2014'}>}Counter))<=5,client_id),client_id)
Expression should be
SUM({<order_date_add_MonthName = {'$(=Month(Today()))'}>}Counter)
Hope this help....
Hi Manish,
Thanks for your answer!
Correct me if I'm wrong, but I've tried the following expression:
Aggr(IF(
Rank(SUM({<order_date_add_MonthName = {'$(vMonthYesterday)'}>}Counter))<=5,client_id),client_id)
This returns the Top 5 on the global period. However I'm looking for the list of all clients who have been one day in the Top 5.
Hope this makes sense,
Jerome
So you mean to say...
You want Clint vs Total in February
for Any client who are at least once in top 5 for any day in February ....
No necessarily Client vs Total, but yes the list of all guys who've been in the top 5 at least one day.
My last expression actually is not clever, I should have used an aggregation per order_date_add
Thanks,
Jerome