Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Chart Total issue with percentages

I have chart similar to what's below.

TypeAttemptCompletion% Completion
TOTAL201263% INSTEAD OF 60%

Type A

161062% - THIS IS ACCURATE
Type B4250% - THIS IS ACCURATE

In this example, The Attempt and Completion columns are based on a user interactions my client does with certain customers. Not all customer receive this user interaction. If the customer receives this interaction they are counted as an attempt and if they do a "particular thing" after that interaction, they are considered a completion. My issue is that a single customer can receive multiple instances of this user interaction. For example, a single customer could experience Type A and Type B customer interaction. However, when I divide Completion/Attempt in my % Completion total, it ignores this thought process. The % Completion only counts this as 1 attempt instead of 2.  In essence, the equations are the following for each column:

Attempt = sum of all customer interactions for unique customers

Completion = count of unique customers who receive a customer interaction and do a particular thing

% Completion = Completion / Attempt

I'm unable to show actual code, so please let me know if more clarification is needed.

5 Replies
Highlighted
Champion III
Champion III

Re: Chart Total issue with percentages

Need little more information on this? what are the values you are getting in your %Completion expression? Try like

% Completion

= sum(DISTINCT Customerinteraction) / Count(Distinct CompletionStatus)

Something like this or if you can scramble your data and upload here, it would be easy to work on...

Highlighted

Re: Chart Total issue with percentages

I agree with vishsaggi‌... Adding Distinct should resolve your issue.

Highlighted
Not applicable

Re: Chart Total issue with percentages

I apologize if I'm unable to provide more information. Hopefully this helps. Thanks so much for the help!!


Attempts = count the distinct customers where Interaction Type is not 'Not a Type'

Completions = count the distinct customers where interaction is not 'Not a Type' and Attribute = Green

% Completion = Attempts / Completions


Give these equations a single caller could experience Type A and Type B and be counted as 1 in the column for each. However, by using this formula, the total will show only 1 customer. I went to the "Total Mode" feature for the Attempts Expression and changed from "Expression Total" to "Sum of Rows" and this fixed the formula for Attempts. However, I'm unsure how to fix this issue for the % Completion expression.


I want to take the sum of Attempt and divide by the Sum of completions for the % Completion expression.

Interaction Type  Column      

Type A

Type B

Type C

Not a Type

Highlighted
Champion III
Champion III

Re: Chart Total issue with percentages

Can you show us the actual formulas you are using for all these 3 expressions? Quite did not get you what you are expecting?

Highlighted
Not applicable

Re: Chart Total issue with percentages

I was able to figure it out. Thanks for the help everyone!

-Seth