Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Pericator
New Contributor III

Percentage Calculation in Cohort Analysis

Hello,

I built a Cohort Analysis Table like the image below. But I cant figure out, how I am able to calculate the percentage values of each column and row in Comparison to the first value in the row. 

E.G.: In the 'Juni-2017' column I want the Percentage difference between 2215 and 2159 which is roughly 97%. In the row below I want 100% because 81 is 100% of itself. 

 

Thanks in Advance 

 

1 Solution

Accepted Solutions
Pericator
New Contributor III

Re: Percentage Calculation in Cohort Analysis

I got it to work with :

left([Member], 1000) / count(distinct total <YEARMONTH>{<isMember={1}>}DEALER_ID) 

 

YEARMONTH is the upper Dimension. The 1000 is just way overgrowth to make sure, it takes all digits in the field.

 

Sadly the same doesnt work for my second Pivot, although its exactly the same just with Fees instead of Members. E:G.:

Fee is: Sum({isMember={1}}DBD_LISTING_FEES)

and % therefore should be: left([Listing Fee], 1000)  / Sum(total <YEARMONTH>{isMember={1}}DBD_LISTING_FEES)

 

But this doesnt work, dont know why though.

 

 

View solution in original post

5 Replies

Re: Percentage Calculation in Cohort Analysis

What is your expression for Member here?

Pericator
New Contributor III

Re: Percentage Calculation in Cohort Analysis

Count({<isMember = {1}>}DEALER_ID)

Re: Percentage Calculation in Cohort Analysis

May be this

Alt(
  Count({<isMember = {1}>}DEALER_ID)/Before(Count({<isMember = {1}>}DEALER_ID))
, 1)
Pericator
New Contributor III

Re: Percentage Calculation in Cohort Analysis

alt ( ... 

Before(Count({<isMember = {1}>}DEALER_ID))

)

 

just returns the value, one column before and not the first in the row. 

Pericator
New Contributor III

Re: Percentage Calculation in Cohort Analysis

I got it to work with :

left([Member], 1000) / count(distinct total <YEARMONTH>{<isMember={1}>}DEALER_ID) 

 

YEARMONTH is the upper Dimension. The 1000 is just way overgrowth to make sure, it takes all digits in the field.

 

Sadly the same doesnt work for my second Pivot, although its exactly the same just with Fees instead of Members. E:G.:

Fee is: Sum({isMember={1}}DBD_LISTING_FEES)

and % therefore should be: left([Listing Fee], 1000)  / Sum(total <YEARMONTH>{isMember={1}}DBD_LISTING_FEES)

 

But this doesnt work, dont know why though.

 

 

View solution in original post