topic Re: creating a dimension of cumulative values on different level elsewhere in QlikView

creating a dimension of cumulative values on different level elsewhere

Anonymous — Mon, 06 Feb 2017 14:10:33 GMT

Hello,

here is a tough one.

I have calculated a pareto class for customers within a sheet object (which is affected by selection i make):

1, calculated cumulative sales for sorted sales per customer:

RangeSum(Above(Sum(sales), 0, RowNo()))

2. derived pareto clas from this with:

if([Cumulative spend]/sum(total sales)<=0.8,'A',

if([Cumulative spend]/sum(total sales)<=0.95,'B','C'))

-> IS THERE A WAY to create a table showing only A, B, C as dimension and calculating spend, no of customers etc based on the above in another table where that has been done on customer level?

thanks

Re: creating a dimension of cumulative values on different level elsewhere

sunny_talwar — Mon, 06 Feb 2017 14:12:17 GMT

If you have more recent version of QlikView, you can def. do it. Look here:

Recipe for a Pareto Analysis – Revisited

Re: creating a dimension of cumulative values on different level elsewhere

Anonymous — Thu, 09 Mar 2017 16:50:06 GMT

hello Sunny,

how can i get this to respond to i.e. year selection or any other?

=Aggr(

If(Rangesum(Above(Sum({1} Sales)/Sum({1} total Sales),1,RowNo()))<0.8, 'A',

If(Rangesum(Above(Sum({1} Sales)/Sum({1} total Sales),1,RowNo()))<0.9, 'B',

'C')),

(Product,(=Sum({1} Sales),Desc))

)

Re: creating a dimension of cumulative values on different level elsewhere

sunny_talwar — Thu, 09 Mar 2017 17:08:29 GMT

May be this:

=Aggr(

If(Rangesum(Above(Sum({1<Year = p(Year)>} Sales)/Sum({1<Year = p(Year)>} total Sales),1,RowNo()))<0.8, 'A',

If(Rangesum(Above(Sum({1<Year = p(Year)>} Sales)/Sum({1<Year = p(Year)>} total Sales),1,RowNo()))<0.9, 'B',

'C')),

(Product,(=Sum({1} Sales),Desc))

)

=Aggr(

If(Rangesum(Above(Sum({1<Year = p(Year)>} Sales)/Sum({1<Year = p(Year)>} total Sales),1,RowNo()))<0.8, 'A',

If(Rangesum(Above(Sum({1<Year = p(Year)>} Sales)/Sum({1<Year = p(Year)>} total Sales),1,RowNo()))<0.9, 'B',

'C')),

(Product,(=Sum({1<Year = p(Year)>} Sales),Desc))

)