Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
jmclaren
Contributor III
Contributor III

How to add totals to a table

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.

MemberIDMemberName
AJohn Smith
BDave Jones
CSandra Evans
DJim Davis
EWendy Rutter

MemberIDEventDate
A01/01/2009
A12/11/2009
A08/01/2011
A10/01/2011
A01/08/2011
A16/04/2013
A09/08/2013
A11/09/2013
A20/12/2013
A21/01/2014
A31/01/2015
B08/01/2009
B01/07/2009
B12/10/2009
B10/01/2011
B01/08/2012
B09/08/2013
B11/09/2013
B20/12/2013
B21/01/2014
B31/01/2014
B16/04/2014
D09/08/2013
D11/09/2013
D20/12/2013
D16/04/2014
D21/01/2015
D31/01/2015
E01/01/2009
E12/11/2010
E08/01/2011
E10/01/2011
E01/08/2012
E16/04/2013
E09/08/2013
E11/09/2013
E20/12/2013
E21/01/2014
E31/01/2015

...and what I'm trying to get to is something like:

MemberIDMemberName2015Total(CurrentYear)2014 Total (Current Year -1)2013 Total (Current Year -2)TotalEvents
AJohn Smith11411
BDave Jones03311
CSandra Evans0000
DJim Davis2136
EWendy Rutter11411

Can anyone suggest how I can achieve this? I've attached a sample file for it. Thank you.

1 Solution

Accepted Solutions
reshmakala
Creator III
Creator III

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

View solution in original post

9 Replies
reshmakala
Creator III
Creator III

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

Please check

jpapador
Partner
Partner

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

MemberIDMemberNameEventYearNumberofEvents
AJohn Smith20092
AJohn Smith20113
AJohn Smith20134
AJohn Smith20141
AJohn Smith20151
BDave Jones20093
BDave Jones20111
BDave Jones20121
BDave Jones20133
BDave Jones20143
CSandra Evans
DJim Davis20133
DJim Davis20141
DJim Davis20152
EWendy Rutter20091
EWendy Rutter20101
EWendy Rutter20112
EWendy Rutter20121
EWendy Rutter20134
EWendy Rutter20141
EWendy Rutter20151

Thanks for your reply.

Jim

maxgro
MVP
MVP

PFA

expression is

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

1.jpg

jpapador
Partner
Partner

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
Creator III
Creator III

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

View solution in original post

jmclaren
Contributor III
Contributor III
Author

Reshma,

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

Many thanks

Jim

jmclaren
Contributor III
Contributor III
Author

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.