Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
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.