Set Dimension value based on the Expression values

Anonymous — Wed, 21 Dec 2016 10:16:02 GMT

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

Re: Set Dimension value based on the Expression values

Anonymous — Thu, 22 Dec 2016 05:29:15 GMT

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

topic Re: Set Dimension value based on the Expression values in QlikView

Set Dimension value based on the Expression values

Re: Set Dimension value based on the Expression values