Set Dimension value based on the Expression values

[Anonymous]

Hi

I have a pivot table in Qlikview as shown below. I need to set a dimension value based on the gaps in the Year Month selected by the user. If the Gap is >=3, need the dimension to set as Return otherwise the dimension value should be Active.

In short, any customer who is not giving continuous business for more than 3 months to be identified and marked.

Thanks.,

Ashmi

[Anonymous]

Just came up with this expression recently yet the requirement is not completely achieved for Return

"Customers who have purchased first and then atleast 3months gap and starting purchasing again is what is required". Need to get 3 month gap between 2 purchases...

=aggr(
if(count(distinct TOTAL Month)=
aggr(if(sum(Sales)>0,count(distinct Aggr(distinct Rank(Sum(Sales),4),Customer,Year,Month))
-(count(distinct {$<Year={$(=Only(Year))}>} if(aggr(sum({$<Year={$(=Only(Year))}>} Sales),Customer,Year,Month)=0,Month)))),Customer),'Active',
if((count(DISTINCT TOTAL Month)-(count(distinct {$<Year={$(=Only(Year))}>} if(aggr(sum({$<Year={$(=Only(Year))}>} Sales),Customer, Year,Month) > 0,Month))))>=(count(DISTINCT TOTAL Month)/3),
'Return','Active')),Customer)

Pls help me sort this issue

Thanks.,

Ashmi