topic Re: Aggr - Help required in QlikView

Aggr - Help required

manojkulkarni — Mon, 13 Jul 2015 06:45:15 GMT

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 ?

Re: Aggr - Help required

manojkulkarni — Mon, 13 Jul 2015 06:58:18 GMT

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

Re: Aggr - Help required

tresB — Mon, 13 Jul 2015 07:04:57 GMT

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?

Re: Aggr - Help required

manojkulkarni — Mon, 13 Jul 2015 07:14:13 GMT

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.

Re: Aggr - Help required

tresB — Mon, 13 Jul 2015 07:37:59 GMT

Something like:

Count(

If(

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

, CustID

)

Re: Aggr - Help required

manojkulkarni — Mon, 13 Jul 2015 07:43:21 GMT

I am doing samething, but now working.

Re: Aggr - Help required

tresB — Mon, 13 Jul 2015 07:49:19 GMT

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

Re: Aggr - Help required

manojkulkarni — Mon, 13 Jul 2015 07:51:35 GMT

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

Re: Aggr - Help required

manojkulkarni — Mon, 13 Jul 2015 07:52:05 GMT

typo mistake, it is not working

Re: Aggr - Help required

mario-sarkis — Mon, 13 Jul 2015 07:54:45 GMT

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.

Re: Aggr - Help required

mario-sarkis — Mon, 13 Jul 2015 08:03:38 GMT

vMaxDate=num(Today())

Count(

If(

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

, CustID

)

Re: Aggr - Help required

manojkulkarni — Mon, 13 Jul 2015 08:21:28 GMT

Not working. Same tried

Re: Aggr - Help required

tresB — Mon, 13 Jul 2015 08:32:17 GMT

PFA

Re: Aggr - Help required

manojkulkarni — Mon, 13 Jul 2015 08:41:45 GMT

For it is not working. We are close to solution.

I need to consider certain transactions only, that means i have set analysis to filter date while taking maximum. How should we do this ?

Re: Aggr - Help required

manojkulkarni — Mon, 13 Jul 2015 08:53:50 GMT

I need to show only customers who made transaction between 15-12-2014 & 31-03-2015, in this case i should get count 2

Re: Aggr - Help required

tresB — Mon, 13 Jul 2015 08:56:21 GMT

Count(

If(

(vMaxDate - aggr(max({<Date={'>=15-12-2014 <= 31-03-2015'}>}Date),CustId,CustId))>90

, CustId

)

Note: the loaded date format and this set date format (DD-MM-YYYY) have to be same.

Re: Aggr - Help required

manojkulkarni — Mon, 13 Jul 2015 09:11:15 GMT

tried same thing, it is not working.

Re: Aggr - Help required

manojkulkarni — Mon, 13 Jul 2015 09:17:33 GMT

In fact, tried same in the application provided by you. It is not working..

Pls could help me

Re: Aggr - Help required

tresB — Mon, 13 Jul 2015 09:17:43 GMT

Could you share your sample app?

Re: Aggr - Help required

manojkulkarni — Mon, 13 Jul 2015 09:20:21 GMT

Attached Sample with modified expresion