Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Highlighted
alif
New 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
anushree1
Valued Contributor

Re: Total column in Script

Please check the attached

7 Replies
Shubham_Deshmukh
Valued Contributor

Re: Total column in Script

can u share this sample small data
alif
New Contributor

Re: Total column in Script

 
Shubham_Deshmukh
Valued Contributor

Re: Total column in Script

I cannot open qvw here, share sample data of your your snapshot
alif
New 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

anushree1
Valued Contributor

Re: Total column in Script

Please check the attached

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)
alif
New Contributor

Re: Total column in Script

Thank you all.