Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Ankhi
Creator
Creator

How to summarize and show expression totals in Pivot table

Hi All,

I have to create a pivot table like below. I have 8 expressions and 3 dimensions.  A sample  requirement is below.

I have created the NSC and TSC part , but I have to create a summary part as well. 

So in the summary for Programme A the YTD should TSC-A + NSC-A(100+200=300),

similarly for B it should be TSC-B + NSC-B.(500 + 100=600). 

Is this possible ? Please can any one help?

Category ProgrammeManagerYTD
TSCAX100
 BX500
TSC-Total  600
NSCAX200
 BX100
NSC-Total  300
SummaryAX300
 BX600
Summary Total   900

 

Regards

Ankhi

Labels (2)
6 Replies
Saravanan_Desingh

Try this.

tab1:
LOAD * INLINE [
    Category, Programme, Manager, YTD
    TSC, A, X, 100
    TSC, B, X, 500
    NSC, A, X, 200
    NSC, B, X, 100
];

Dim:
LOAD * INLINE [
    Dim
    1
    2
];

Dimension:

=If(Dim=1,Category,'Summary')

Programme

Expression: Sum(YTD)

Saravanan_Desingh

Output:

commQV55.PNG

Lisa_P
Employee
Employee

Using QlikView expand Category, go to chart properties, Presentation and for both Category and Programme, select Show Partial Sums.

Ankhi
Creator
Creator
Author

Thanks Lisa for the reply. I have done that. But I want a summary section which along with Grand Total will show total for each dimension value as given in my table (summary section)  in my 1st post.

Ankhi
Creator
Creator
Author

Hi Saran, 

Thanks for your reply. But I don't think  I am getting you. Below is the actual dimensions that I am using

=if([Category]='Labour','TSC','NSC')--->This is the Category

Programme

Programme Manager

Expressions: I have got 8 expression like below. Just given 1 for e.g.

YTD=Sum({<
[Flag]={'ACT'},
[F_DATE] = {"<=$(=max(F_DATE))"},
>}Cost)

Below bold rows are what  I need. 

Expenditure CategoryProgrammeProgramme ManagerITD Actuals
Summary Total  1000
TSCTotal 300
TSCAX100
TSCBX200
NSCTotal 700
NSCAX300
NSCBX400
Summary Total  1000
SummaryAX400
SummaryBX600

 

Regards

Ankhi

Brett_Bleess
Support (Former)
Support (Former)

Would likely be most helpful for you to attach the QVW to the thread if you want further assistance, as I think folks are likely still confused as to what exactly you are trying to do.  This way they will have access to the data model as well as the sheet objects and properties to check everything.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.