Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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
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?
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.
Thanks for your help guys.
=aggr(firstsortedvalue(Answer, Date * -1), Customer)
andthen
count(distinct Customer)
worked like a charm.