Aggr - Help required

[manojkulkarni]

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 ?

[manojkulkarni]

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))

[tresesco]

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?

[manojkulkarni]

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.

[tresesco]

Something like:

Count(

               If(

                         (vMaxDate - aggr(max(tranDate),CustID,ShopID))>40

                         , CustID

                    )

               )

[manojkulkarni]

I am doing samething, but now working.

[tresesco]

Great ! Now could you please close this thread by marking it correct/helpful?

[manojkulkarni]

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

[manojkulkarni]

typo mistake, it is not working

[mario-sarkis]

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.