Pie chart showing percentage of answers

[#1]

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 🙂

M.

[martin59]

Hello,

You have to check the relative option in expression tab.

Look the attachement,

Regards,

Martin

[#3]

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.

[#4]

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

[johnw]

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?

[Oleg_Troyansky · Accepted solution]

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.

[#7]

Thanks for your help guys.

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

andthen

count(distinct Customer)

worked like a charm.