Find the no. of zero values in pivot table

[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

[Gysbert_Wassenaar]

Please post a small Qlikview document with (example) data that illustrates the problem.

[Anonymous]

can yu share sample data?

[Anonymous]

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)

[Anonymous]

I need help on Return Customers alone. Need to find 3 months gap otherwise "-" is fine.

[Gysbert_Wassenaar]

Perhaps like in the attached example.