create a new calculated field

[Nils9]

Hello,

I am completely new to Qlik, and appreciate your help in solving my query:

I have a straight table with 4 columns. In one of the columns, I would like to group few values and create a new row with their total value.

Attached is my sample table and how i expect it to be.

I went through few previous questions, and the manual, but unable to get the desired output.

Note that I do not wish to group all values in that column, only a few, for eg. Extra remains the same, i dont want to group it.

Many thanks in advance,
Nilofar

[justISO · Accepted solution]

Hi, try something like this:

temp:
LOAD
    Year,
    Month,
    Department,
    Count
FROM [lib://AttachedFiles/Book3.xlsx] (ooxml, embedded labels, header is 1 lines, table is Sheet1)
WHERE len(Year)>0; //to ignore empty rows

CONCATENATE //adding this part to above loaded table
LOAD
    Year,
    Month,
    'Support (Admin + Audit)' as Department, //creating new 'Department'
    sum(Count) as Count //making aggregated sum
FROM [lib://AttachedFiles/Book3.xlsx] (ooxml, embedded labels, header is 1 lines, table is Sheet1)
WHERE match(Department, 'Audit', 'Admin') //taking only these 2 Departments
GROUP BY Year, Month; //for sum(Count) aggregation

[Nils9]

This works, thanks a lot!
Also can you tell me if i want to add another group here, how do i do that?
For ex. group Extra and any other department?

[justISO · Accepted solution]

you just add additional concatenate script part (modified 2nd part of script above) below all written script I provided earlier. So basically for each new group you make a new calculation and add that to your main table. This can be do something like this, where bold parts should be modified according your needs:

... script I provied ealier ... + below part for new group. You repeat this for each new group ..

CONCATENATE
LOAD
Year,
Month,
'NAME OF DEPARTMENT' as Department, //rename 'NAME OF DEPARTMENT' to your need
sum(Count) as Count
FROM [lib://AttachedFiles/Book3.xlsx] (ooxml, embedded labels, header is 1 lines, table is Sheet1)
WHERE match(Department, 'XXXX', 'YYYY') //define what actual departments from main table you want to group.
GROUP BY Year, Month;

[Nils9]

Thank you !!

[Nils9]

One last query for the above please?

If I want to display a chart with drilldown option, where the filterpane shows me only groups as Department1, Department2, and upon any selection, it shows the individual department, like Admin, Audit, etc. 

Can you help please?