Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Total column in Script

Hello, I'm new to Qlikview so this might be easy.

I have a set of data the includes several KPIs for several companies per month. Below is a simplified example showing 3 KPIs for 2 companies in 2 months.

Capture.PNG

I want to add a 'Total' column to show the total value of a KPI for all companies in a certain month.

For example: in April both companies had a revenue of 1; so the total column should equal 2 for all rows that have:

  • Month = April
  • KPI = Revenue

Similarly, the total column would equal:

  • 8 for Revenue rows in May
  • 4 for Units rows in April

Capture1.PNG

 

I used the method described in this thread https://community.qlik.com/t5/New-to-QlikView/Load-Script-Add-a-total-column/m-p/503083 but that just gave me a total of all KPI for all companies in all months.

 

Any help is appreciated. Thanks in advance.

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

Re: Total column in Script

Please check the attached

View solution in original post

7 Replies
Highlighted
Specialist
Specialist

Re: Total column in Script

can u share this sample small data
Highlighted
Contributor
Contributor

Re: Total column in Script

 
Highlighted
Specialist
Specialist

Re: Total column in Script

I cannot open qvw here, share sample data of your your snapshot
Highlighted
Contributor
Contributor

Re: Total column in Script

Thank you for replying. I've attached an excel sheet with the sample data and below is a simplification of my script

Table1:
LOAD Month,
Company,
KPI,
Data

FROM
Sample.xlsx
(ooxml, embedded labels);

Join (Table1)
Load
sum(Data)
Resident Table1

Highlighted
Specialist II
Specialist II

Re: Total column in Script

Please check the attached

View solution in original post

Highlighted

Re: Total column in Script

Try this

Table1:
LOAD Month, 
     Company, 
     KPI, 
     Data
FROM Sample.xlsx
(ooxml, embedded labels);

Join (Table1)
LOAD Month,
	 KPI,
	 Sum(Data) as Total
Resident Table1
Group By Month, KPI;

or try this in the front end as an expression

Sum(TOTAL <Month, KPI> Data)
Highlighted
Contributor
Contributor

Re: Total column in Script

Thank you all.