Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
OaksEnd
Contributor II
Contributor II

Sum of two separate Counts

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

8 Replies
patilamay
Contributor III
Contributor III

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 
aOwnerTom
aSupportclaire
bOwnerclaire
bSupportJoe
cOwnerJohn
cSupportTom

 

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

Hope this helps.

Thanks,

Amay

OaksEnd
Contributor II
Contributor II
Author

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

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

Thanks,

OaksEnd
Contributor II
Contributor II
Author

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

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

Thanks,

 

OaksEnd
Contributor II
Contributor II
Author

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

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.