Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community!
I am facing this issue: I have created a measure that generates nominal results, in other words, letters.
Here an example:
load*inline
[date,customer,
29/10/2016,'a',
29/11/2016,'a'
29/12/2016,'a'
12/10/2016,'b',
13/10/2016,'b'
14/10/2016,'b'
]
;
I wanted to mark the customers that are not appearing in this way: generally customer x appear in average every day, it is 3 day that does not appear, this is a problem. Here we go:
The measure are these:
last appear, the last date of the customer :
max([date])
dayFromLast, day from the last appearance of the customer (use 03/01/2017, DD/MM/YYYY):
(today()-max(date))
EDIT:
GeneralBehaviour, how often the customer appears (average):
(max([date])- min([date]))/(count(customer)-1)
EDIT:
risk, if the days of the customer's last appearance is bigger than the GeneralBehaviour (bad) or viceversa (good)
if((max([date])- min([date]))/(count(customer)-1)>(today()-max(date)),'good','bad')
My question is: could I have risk as dimension, so I can put it in a filter? If so, how? Logically putting it in a filter is not possible. I know that I can solve it adding a column in the data load, but I'd like to do it in the app without touching the data load.
Thanks in advance.
1st a question for you.... Count(DISTINCT Customer) will always be 1, no? I mean Customer is a dimension, what is the point of dividing by DISTINCT Customer? May be you Want Count(Customer)
Coming back to your question, try this:
Aggr(If((Max({1}[date]) - Min({1}[date]))/Count({1}DISTINCT customer) > (Today() - Max({1}date)), 'good', 'bad'), customer)
1st a question for you.... Count(DISTINCT Customer) will always be 1, no? I mean Customer is a dimension, what is the point of dividing by DISTINCT Customer? May be you Want Count(Customer)
Coming back to your question, try this:
Aggr(If((Max({1}[date]) - Min({1}[date]))/Count({1}DISTINCT customer) > (Today() - Max({1}date)), 'good', 'bad'), customer)
Hi! Thanks a lot, I have corrected the formulae, and your result works fine.