Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm new to Qlikview and struggling to produce the data I need. Basically, I have a list of members in one table and a list of event dates they have attended in another. I want to append new fields to the member records that give a summary total of events they have participated in: the current year, current year -1, current year-2 and total number of events.
MemberID | MemberName |
A | John Smith |
B | Dave Jones |
C | Sandra Evans |
D | Jim Davis |
E | Wendy Rutter |
MemberID | EventDate |
A | 01/01/2009 |
A | 12/11/2009 |
A | 08/01/2011 |
A | 10/01/2011 |
A | 01/08/2011 |
A | 16/04/2013 |
A | 09/08/2013 |
A | 11/09/2013 |
A | 20/12/2013 |
A | 21/01/2014 |
A | 31/01/2015 |
B | 08/01/2009 |
B | 01/07/2009 |
B | 12/10/2009 |
B | 10/01/2011 |
B | 01/08/2012 |
B | 09/08/2013 |
B | 11/09/2013 |
B | 20/12/2013 |
B | 21/01/2014 |
B | 31/01/2014 |
B | 16/04/2014 |
D | 09/08/2013 |
D | 11/09/2013 |
D | 20/12/2013 |
D | 16/04/2014 |
D | 21/01/2015 |
D | 31/01/2015 |
E | 01/01/2009 |
E | 12/11/2010 |
E | 08/01/2011 |
E | 10/01/2011 |
E | 01/08/2012 |
E | 16/04/2013 |
E | 09/08/2013 |
E | 11/09/2013 |
E | 20/12/2013 |
E | 21/01/2014 |
E | 31/01/2015 |
...and what I'm trying to get to is something like:
MemberID | MemberName | 2015Total(CurrentYear) | 2014 Total (Current Year -1) | 2013 Total (Current Year -2) | TotalEvents |
A | John Smith | 1 | 1 | 4 | 11 |
B | Dave Jones | 0 | 3 | 3 | 11 |
C | Sandra Evans | 0 | 0 | 0 | 0 |
D | Jim Davis | 2 | 1 | 3 | 6 |
E | Wendy Rutter | 1 | 1 | 4 | 11 |
Can anyone suggest how I can achieve this? I've attached a sample file for it. Thank you.
This might not be the optimal solution but this may work.
Please check
Try the following in the load script:
MemberTable:
LOAD
MemberId,
MemberName
FROM Data
Left Join (MemberTable)
LOAD
MemberID,
Count(EventDate) as NumberofEvents
Year(EventDate) as EventYear
From Data
Group By MemberID, Year(EventDate);
Hi Reshma
Thanks for the quick reply. I should have said - the objective here is to identify those with low or non-existant participation, so it's important that values of zero, where applicable, are written to each field for each member. I imagined this would be done in the script so found your suggestion using expressions in an object interesting. Could they be modified to include zero values for member 'C'?
Thanks
Jim
Hi jpapador,
I'm not sure if I followed your instructions correctly, but I ended up with multiple records for each member instead of a single record for each with new columns. Did I do this right?
..added
Left Join
LOAD MemberID,
Count(EventDate) as NumberofEvents,
Year(EventDate) as EventYear
resident Events
Group By MemberID, Year(EventDate);
..got
MemberID | MemberName | EventYear | NumberofEvents |
A | John Smith | 2009 | 2 |
A | John Smith | 2011 | 3 |
A | John Smith | 2013 | 4 |
A | John Smith | 2014 | 1 |
A | John Smith | 2015 | 1 |
B | Dave Jones | 2009 | 3 |
B | Dave Jones | 2011 | 1 |
B | Dave Jones | 2012 | 1 |
B | Dave Jones | 2013 | 3 |
B | Dave Jones | 2014 | 3 |
C | Sandra Evans | ||
D | Jim Davis | 2013 | 3 |
D | Jim Davis | 2014 | 1 |
D | Jim Davis | 2015 | 2 |
E | Wendy Rutter | 2009 | 1 |
E | Wendy Rutter | 2010 | 1 |
E | Wendy Rutter | 2011 | 2 |
E | Wendy Rutter | 2012 | 1 |
E | Wendy Rutter | 2013 | 4 |
E | Wendy Rutter | 2014 | 1 |
E | Wendy Rutter | 2015 | 1 |
Thanks for your reply.
Jim
PFA
expression is
count({$ <EventDate={">=$(=YearStart(addmonths(Today(),-24)))<=$(=YearEnd(addmonths(Today(),-24)))"}>} DISTINCT EventDate)
Yes with that source table you should now be able to create a straight table chart.
Dimension:
MemberID
Member Name
Expression:
Sum({$<EventYear={'$(=Max(EventYear))'}>} NumberofEvents)
Sum({$<EventYear={'$(=Max(EventYear)-1)'}>} NumberofEvents)
Sum({$<EventYear={'$(=Max(EventYear)-2)'}>} NumberofEvents)
Sum(NumberofEvents)
If you wanted to keep the date level detail you could also just add a year field and forget the join:
EventTable:
LOAD MemberID,
EventDate,
Year(EventDate) as EventYear
resident Events;
You expressions would change slightly to:
Expression:
Count({$<EventYear={'$(=Max(EventYear))'}>} EventDate)
Count({$<EventYear={'$(=Max(EventYear)-1)'}>} EventDate)
Count({$<EventYear={'$(=Max(EventYear)-2)'}>} EventDate)
Count(EventDate)
Your source tables would remain the same as your original post you are simply just adding a year field to the event table.
You can uncheck "Suppress Zero Values" in chart properties.
Reshma,
That looks just like what I need (once I figured out how to enter the 'curyear' variables ).
Many thanks
Jim
Similar solutions for the same problem - all of which are good examples for me to learn from. The solution from Reshma behaves the way I prefer when selecting members.
Thanks for all the help.