Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Please post a small Qlikview document with (example) data that illustrates the problem.
can yu share sample data?
Hi,
Please find the sample attached.
I have already come up with something like below. Please feel free to invalidate the expression. Whats required is (pls refer the snapshot below) there should be a minimum of 3 months gap between the purchases and to be term it as return. Otherwise can be marked as "-".
=aggr(
if(count(distinct TOTAL Month)=
aggr(if(sum(PrdQty)>0,count(distinct Aggr(distinct Rank(Sum(PrdQty),4),Customer, Year,Month))
-(count(distinct {$<Year={$(=Only(Year))}>} if(aggr(sum({$<Year={$(=Only(Year))}>} PrdQty),Customer,Year,Month)=0,Month)))),Customer),'Active',
if((count(DISTINCT TOTAL Month)-(count(distinct {$<Year={$(=Only(Year))}>} if(aggr(sum({$<Year={$(=Only(Year))}>} PrdQty),Customer, Year,Month) > 0,Month))))>=(count(DISTINCT TOTAL Month)/3),
'Return','Active')),Customer)
I need help on Return Customers alone. Need to find 3 months gap otherwise "-" is fine.
Perhaps like in the attached example.