Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
smilingjohn
Specialist
Specialist

Difficult Req

Hi All , 

Please find the attachment of the excel sheet , 

I want to show subtotal and Total  in a table in QlikView . 

I have Department and Category and Sales , 

As shown in the screenshot below is it possible to achive this in Qlikview ? 

 

 

Thanks in Advcance 

1 Solution

Accepted Solutions
Saravanan_Desingh

Can you try this?

tab1:
LOAD * INLINE [
    Department , Category, Saes
    Sales, A, 10
    IT, A, 20
    HR, A, 30
    Finance, A, 10
    Sales, B, 5
    IT, B, 10
    HR, C, 15
    Finance, D, 15
];

Concatenate(tab1)
LOAD DISTINCT 'SubTotal' As Department, Category
Resident tab1;

tab2:
LOAD * INLINE [
    Department, Dim
    Sales, 1
    IT, 1
    HR, 1
    SubTotal, 2
    Finance, 3
];

Dimensions: Department & Category

Exp: 

If(Department='SubTotal', 
RangeSum(Above(total Sum(Saes) , 0, 4)),
Sum(Saes)
)

commQV80.PNG

View solution in original post

9 Replies
smilingjohn
Specialist
Specialist
Author

Hi community any help on this .

 

smilingjohn
Specialist
Specialist
Author

Hi community 

Is my requirements possible in qlikview or not ? 

Saravanan_Desingh

Are you looking something like this? Do you want Subtotal before Finance?

tab1:
LOAD * INLINE [
    Department , Category, Saes
    Sales, A, 10
    IT, A, 20
    HR, A, 30
    Finance, A, 10
    Sales, B, 5
    IT, B, 10
    HR, C, 15
    Finance, D, 15
];

tab2:
LOAD * INLINE [
    Department, Dim
    Sales, 1
    IT, 1
    HR, 1
    Finance, 2
];
Saravanan_Desingh

commQV79.PNG

smilingjohn
Specialist
Specialist
Author

Hi @Saravanan_Desingh ,

How are you getting this in pivot table can you please attach the qlikview file ??? And also  after finance there should not be subtotal  . How can it be done.

Thanks 

Saravanan_Desingh

Please find the file here.

smilingjohn
Specialist
Specialist
Author

@Saravanan_Desingh  

Thanks for the file , How should we remove the subtotal after Finance ?  

Saravanan_Desingh

Can you try this?

tab1:
LOAD * INLINE [
    Department , Category, Saes
    Sales, A, 10
    IT, A, 20
    HR, A, 30
    Finance, A, 10
    Sales, B, 5
    IT, B, 10
    HR, C, 15
    Finance, D, 15
];

Concatenate(tab1)
LOAD DISTINCT 'SubTotal' As Department, Category
Resident tab1;

tab2:
LOAD * INLINE [
    Department, Dim
    Sales, 1
    IT, 1
    HR, 1
    SubTotal, 2
    Finance, 3
];

Dimensions: Department & Category

Exp: 

If(Department='SubTotal', 
RangeSum(Above(total Sum(Saes) , 0, 4)),
Sum(Saes)
)

commQV80.PNG

Saravanan_Desingh

In the Sort, use Dim.

commQV81.PNG