Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I am doing some aggregate at chart level to identify the customer last visit at shop level.
My dimension is Shop Id and expression is to get max transaction date of each user per shop.
I am calculating difference using maximum date at global & maximum date derived from aggregate at user/shop level.
This works only when I add user ID in dimension, but i doesn't want to show userid in dimension.
How this can be solved ?
You probably missed out 'date format' note. PFA
can i use something like in expression
Dimension is : ShopID
this is to calculate days gap in shopping. vMaxDate is fixed date.
(vMaxDate - aggr(max(tranDate),CustID,ShopID))
There would be multiple customers against a shop, hence multiple max dates/differences for them. How would would like to see those differences agains one shopid? Concatenated or summed up?
Exactly, there will be multiple customers for each shop. I want to calculate what this last purchase of each customer per store & get difference w.r.t today's date. Then take the count of customers who's difference is 40 days per store.
Something like:
Count(
If(
(vMaxDate - aggr(max(tranDate),CustID,ShopID))>40
, CustID
)
)
I am doing samething, but now working.
Great ! Now could you please close this thread by marking it correct/helpful?
Below is the sample data.
vMaxDate = 2015-03-31
Expected Result:
StoreID | Customers whose Recency became >90 Day |
15 | 3 |
Sample Data:
ShopId | CustId | txn_date |
15 | 1 | 5/9/2013 |
15 | 1 | 6/10/2012 |
15 | 1 | 9/28/2013 |
15 | 1 | 12/29/2014 |
15 | 2 | 6/20/2013 |
15 | 2 | 1/31/2010 |
15 | 2 | 8/20/2013 |
15 | 2 | 11/27/2013 |
15 | 2 | 5/1/2013 |
15 | 2 | 8/3/2013 |
15 | 2 | 12/18/2014 |
15 | 2 | 11/13/2012 |
15 | 2 | 7/5/2010 |
15 | 3 | 12/14/2014 |
typo mistake, it is not working
hi Manoj,
maybe you need to see the date form that you getting from the aggregate function
vMaxDate and the date u getting from the agrregate fucntion should be the same and in you case i think number should be the format.