How to combine or roll up data into a new table

[#1]

Ok, so I am very new to Qlik and I have been tasked to create a dashboard for my company.  I have data from many different sources but at the end of the day each source is in a pretty similar format.  Here is an example of some data:

Segment	IT Head Count	Total Team Member
CRP	3	250
DFW	15	45

So I have been able to pull the data from SharePoint into QlikView and make some charts and display the data as individual segments.  What management would really like to see is a overall view of all of IT and then the ability to drill down into each segment via a slicer or a filter.  So I have the need to roll all of the "Segments" up into a new field called like "All IT" or something like that but still leave all of the other data alone so I can display that if someone wanted a more granular view.

So the new field would look something like this:

Segment	IT Head Count	Total Team Member
CRP	3	250
DFW	15	45
All IT	18	295

Is this something that could be done in QlikView or would I be best manipulating the data before I bring it into Qlik?

If it could be done in Qlik could someone maybe show me an example of how it could be done?

[morganaaron]

Hi Jason,

There are various ways to create groups or totals, but seeing as you're using a straight table chart, the simplest way to just get a total is to go to your Presentation tab, select 'Totals on Last Row' and tick 'Use Label' and insert a label of 'All IT' - then on your Expressions chart ensure each of your expressions has 'Expression Total' selected in the 'Total Mode' box on the bottom right hand side.

[#3]

You also have the option of changing the chart into a Pivot table instead and adding a partial sum to that field to give you the total. Again you can the go to the presentation tab to rename the label for the total

[#4]

My apologies, maybe I didn't explain this correctly.

The above examples are just examples of the raw data before it was pulled into Qlik.  I am mainly displaying the data in Combo and or Bar charts.

For example, I get the above file from our HRIS group for each segment (total of 5) each month.  I then display the data as "IT Headcount" in a Bar Chart.  The chart currently has each individual segment and month as a Dimension and then Total IT Headcount as an expression.  What I really need as the first view would be "All IT" as my Dimension displaying the sum of all 5 segments. 

[#5]

What is your data model looking like at the moment? I think from what you are saying you, it would be best to have a single fact table and use the concatenate function to load each source into the one table. Can add a new IT field into that fact table, or indeed have a side dimension table with that field joined by the a segment key.

Easier if you have an example to show though

[morganaaron]

Hi Jason,

Again there are various ways to do this - it depends on how you want the functionality to work and a few other ideas.

As an example, you could create a cyclic group with two expressions, the first simply a text expression 'All IT' and the second your Segment field. Using this cyclical group as your dimension it would provide the sum/counts over all segments for the text field 'All IT' (as this isn't a dimension as such, more just fooling it into providing one total dimension) and then you could cycle to your segment as your second view.

You can create this "grouping" in your script, but your issue will probably be that you only have one group to put it all into, so you couldn't use the native drill down unless you create a dummy group to sit alongside it.

I'd recommend having a read up on drill down and cyclical groups to see if this will offer what you want. I realise you're new to Qlik so there's not really a one size fits all answer, it'll depend on how you want it to work, but feel free to discuss further and I'll see if I can provide advice!

[#7]

As Aaron says, there is a number of options, If there is any potential scope to grow beyond just IT department then the script option is probably the way to go