Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Pie chart showing percentage of answers

I want to create a pie chart showing percentage of answers. Data structure is something like this:

Customer, Date, Answer

A, 01.01.2010, Yes
A, 14.01.2010, No
A, 18.01.2010, Maybe
B, 18.12.2010, Yes
B, 12.02.2010, Maxbe
C, 22.02.2010, No
D, 22.06.2010, Possibly

Pie chart should show how many time (or percentafe) each answer poped up, but taking into accoung only last round of questions (so latest answer for each cutomer).

I know I have to use aggr, count, and/or firstsortedvalue, but I'm having a hard time figuring out the correct expression.

Thnx for your help Smiley Happy

M.

1 Solution

Accepted Solutions
MVP
MVP

Pie chart showing percentage of answers

Marko,

here is how you do it with aggr and firstsortedvalue:

Calculated Dimension:

=aggr(firstsortedvalue(Answer, Date * -1), Customer)

Logically, it means "the last Answer for each Customer)

Expression:

count(distinct Customer)

which means - count those Customers that gave a specific last Answer.

6 Replies
martin59
Valued Contributor II

Re. :Pie chart showing percentage of answers

Hello,

You have to check the relative option in expression tab.

Look the attachement,

Regards,

Martin

Not applicable

Re. :Pie chart showing percentage of answers

But what if you have this situation:

Customer, Date, Answer
A, 01.01.2010, Yes
A, 14.01.2010, No
A, 18.01.2010, No
B, 18.12.2010, Yes
B, 12.02.2010, Maybe
C, 22.02.2010, No
D, 22.06.2010, Possibly

Answer 'No' should apper only 2 times because only the latest answer for each customer counts. In your solution, 'no' appers 3 times. And 'Yes' from customer A should not be counted because it's not the newest answer from that customer.

Not applicable

Pie chart showing percentage of answers

Hi,

i had the similar sitiation before and the soluti i you are looking for is

count({$ <ANSWER= {'=AGGR(min(DATE),CUSTOMER)'} >} ANSWER)

cheers,

anze

MVP
MVP

Pie chart showing percentage of answers

That doesn't seem to do what we want, but using an advanced search expression inside the set analysis like that seems like a good approach. This seems to work:

LOAD *
,Date&Customer as DateAndCustomer
INLINE [
Customer, Date, Answer
A, 01.01.2010, Yes
A, 14.01.2010, No
A, 18.01.2010, No
B, 18.12.2010, Yes
B, 12.02.2010, Maybe
C, 22.02.2010, No
D, 22.06.2010, Possibly
];

count({<DateAndCustomer={'=Date=aggr(nodistinct max(Date),Customer)'}>} Answer)

But maybe I'm overcomplicating things?

MVP
MVP

Pie chart showing percentage of answers

Marko,

here is how you do it with aggr and firstsortedvalue:

Calculated Dimension:

=aggr(firstsortedvalue(Answer, Date * -1), Customer)

Logically, it means "the last Answer for each Customer)

Expression:

count(distinct Customer)

which means - count those Customers that gave a specific last Answer.

Not applicable

Pie chart showing percentage of answers

Thanks for your help guys.

=aggr(firstsortedvalue(Answer, Date * -1), Customer)

andthen

count(distinct Customer)

worked like a charm.

Community Browser