How to add totals to a table

[jmclaren]

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.

[reshmakala]

This might not be the optimal solution but this may work.

Please check

[jpapador]

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);

[jmclaren]

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

[jmclaren]

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

[maxgro]

PFA

expression is

count({$ <EventDate={">=$(=YearStart(addmonths(Today(),-24)))<=$(=YearEnd(addmonths(Today(),-24)))"}>} DISTINCT EventDate)

[jpapador]

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.

[reshmakala · Accepted solution]

You can uncheck "Suppress Zero Values" in chart properties.

[jmclaren]

Reshma,

That looks just like what I need (once I figured out how to enter the 'curyear' variables ).

Many thanks

Jim

[jmclaren]

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.