topic Re: Find the no. of zero values in pivot table in QlikView

Find the no. of zero values in pivot table

Anonymous — Wed, 25 Nov 2020 16:16:04 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: Find the no. of zero values in pivot table

Gysbert_Wassenaar — Thu, 22 Dec 2016 07:36:10 GMT

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

Re: Find the no. of zero values in pivot table

Anonymous — Thu, 22 Dec 2016 09:30:44 GMT

can yu share sample data?

Re: Find the no. of zero values in pivot table

Anonymous — Fri, 23 Dec 2016 06:42:30 GMT

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)

Re: Find the no. of zero values in pivot table

Anonymous — Fri, 23 Dec 2016 06:43:33 GMT

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

Re: Find the no. of zero values in pivot table

Gysbert_Wassenaar — Fri, 23 Dec 2016 11:21:23 GMT

Perhaps like in the attached example.