Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mikkeltaylor
Contributor II

Accumulate over 2 dimensions - Pivot works, chart incorrect

Hi all,

I'm trying to show the % of customers who buy a product when they buy their next product.  In my data I have First Product, Months until next product (SPD) and the number/% of customers.

I've read a lot of posts on using rangesum(above( and also Pareto techniques.  The pivot table below, works and shows the correct % for each product, but when I change to a chart it seems to accumulate the '%' for each product within the SPD so the lines all appear much closer than it should be.  any help much  appreciated!

Calculation that works in Pivot table:

RangeSum(Above(total( Sum([# of Individuals])/ sum(total <[First Product]>[# of Individuals])), 0, RowNo( TOTAL)))

Correct Pivot

pivot.JPG

Incorrect Chart

Chart.JPG

Thanks

Mike.

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Accumulate over 2 dimensions - Pivot works, chart incorrect

Try this

Aggr(RangeSum(Above(Sum([# of Individuals])/Sum(TOTAL <[First Product]> [# of Individuals]), 0, RowNo())), [First Product], (SPD, (NUMERIC)))

10 Replies
mikkeltaylor
Contributor II

Re: Accumulate over 2 dimensions - Pivot works, chart incorrect

Help please.....

rangam_s
Contributor II

Re: Accumulate over 2 dimensions - Pivot works, chart incorrect

Try This.

RangeSum(Above(Sum({<[First Product]={'CP-HOME'}>}[# of Individuals])/ sum({<[First Product]={'CP-HOME'}>}Total [# of Individuals]), 0, RowNo()))

MVP
MVP

Re: Accumulate over 2 dimensions - Pivot works, chart incorrect

Try this

Aggr(RangeSum(Above(Sum([# of Individuals])/Sum(TOTAL <[First Product]> [# of Individuals]), 0, RowNo())), [First Product], (SPD, (NUMERIC)))

mikkeltaylor
Contributor II

Re: Accumulate over 2 dimensions - Pivot works, chart incorrect

Thank you for responding.  I put that in but unfortunately it did not accumulate across the SPD Rangdam.JPG

mikkeltaylor
Contributor II

Re: Accumulate over 2 dimensions - Pivot works, chart incorrect

Hi Sunny, thank you!!  it looks so close and I even might use it as it is.  I am expecting though that it should reach 100% , but the consideration is that not everyone has a SPD i.e. they do not buy a second product.  I couldn't quite reconcile the numbers I was getting back so wondered if you could see what is missing that is stopping it reach 100% in the set analysis? i.e. I want to include everyone regardless of SPD

Sunny.JPG

MVP
MVP

Re: Accumulate over 2 dimensions - Pivot works, chart incorrect

I don't see the whole pivot table, but does it reach the 100% in the pivot table?

mikkeltaylor
Contributor II

Re: Accumulate over 2 dimensions - Pivot works, chart incorrect

Yes, does in the pivot table at the same points it does in the graphs

MVP
MVP

Re: Accumulate over 2 dimensions - Pivot works, chart incorrect

Would it be possible for you to share a qvf sample with us so that we can take a look into it?

pablolabbe
Valued Contributor II

Re: Accumulate over 2 dimensions - Pivot works, chart incorrect

Don't forget !


When applicable please mark the appropriate replies as CORRECT https://community.qlik.com/docs/DOC-14806. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.