Sum of two separate Counts

[OaksEnd]

I'm new to QLIK Sense, and i'm trying to create a bar charts, where I am looking to display the total tasks owned by team members.  I have already  added a bar chart displaying the Tasks owned "Owner", however team member can also own a task by being "support". So, I want to be able to perform a SUM of 2 counts, to fully show the total tasks owned by Person X.

Can anyone tell me the syntax to use for this?

my table has 3 columns:

Task                  Owner           Support

a                         Tom                  claire

b                        Claire                Joe

c                          John                   Tom

[patilamay]

Hi,

You can use cross table function in qlik and create a new table like this,

CrossTable(Type,Name,1)
LOAD * Inline[
Task,Owner,Support
a,Tom,claire
b,claire,Joe
c,John,Tom
];

This will create a table in this format.

Task	Type	Name
a	Owner	Tom
a	Support	claire
b	Owner	claire
b	Support	Joe
c	Owner	John
c	Support	Tom

Now you can Use name as dimension and Count(task) as measure.

Hope this helps.

Thanks,

Amay

[OaksEnd]

thanks for the reply!

The table I'm working with is an excel file, ~600 line items, and i'd like to set up the connection to refresh on a weekly/monthly basis.

therefore, will the script below still be suitable? or is it only suitable for small tables?

thanks,

Sinead 

[patilamay]

This script will work for large tables too. Just import the excel and use crosstable function over it, you should be good.

Thanks,

[OaksEnd]

I've tried this, but I only included the first few rows of data in the crosstable script.  so it's only summing the data for these rows only.

Do I need to copy/paste enter table into this script? 

[patilamay]

I didn't understand what you mean. Can you share you script?

Thanks,

[OaksEnd]

here is the script I've entered:

CrossTable(Type,Name,1)
LOAD * Inline [
Category,Task Owner,Task Owner_2
Protocols_Updates, Claire, Adele
SLEs_Stability_statements, Grainne, Declan
SLEs_Stability_statements, Adele,  Anne
HA_Filings, Adele
Excel_File_Updates, Anne, Declan
Excel_File_Updates, Nana, Martin
HA_Filings, Nana, Martin
];

the ouput I get on a chart will only include data as above. there are another 200+ line items within the excel file that are not included in the chart  after I plot 'Name' x Count of 'tasks'.

[patilamay]

Instead of "Inline" table use excel as your input.  Maybe like this.

Crosstable (Type, Name, 1)

Load Category,

          Task Owner,

          Task Owner_2

From yourexcel.xls

or share the excel.

Thanks,

[Kushal_Chawda]

if you don't want to increase the data using cross table, use below method

Data:
LOAD * Inline [
Category,Task Owner,Task Owner_2
Protocols_Updates, Claire, Adele
SLEs_Stability_statements, Grainne, Declan
SLEs_Stability_statements, Adele, Anne
HA_Filings, Adele
Excel_File_Updates, Anne, Declan
Excel_File_Updates, Nana, Martin
HA_Filings, Nana, Martin
];

LinkOwner:
Load [Task Owner_2] as [Task Owner],
count([Task Owner_2]) as SupportCount
Resident Data
Group by [Task Owner_2];

Now create chart with Dimension "Task Owner " and expression as below

=rangesum(Count(Category),Sum(SupportCount))

Note: Replace inline table load with actual file load. Also change column name as per your data input.