topic Re: Total column in Script in QlikView

Total column in Script

alif — Sat, 16 Nov 2024 04:01:34 GMT

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.

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

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.

Re: Total column in Script

Shubham_Deshmukh — Thu, 28 Mar 2019 09:55:32 GMT

can u share this sample small data

Re: Total column in Script

alif — Thu, 28 Mar 2019 10:02:36 GMT

Re: Total column in Script

Shubham_Deshmukh — Thu, 28 Mar 2019 10:06:34 GMT

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

Re: Total column in Script

alif — Thu, 28 Mar 2019 10:09:10 GMT

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

Re: Total column in Script

anushree1 — Thu, 28 Mar 2019 11:45:02 GMT

Please check the attached

Re: Total column in Script

sunny_talwar — Thu, 28 Mar 2019 12:00:26 GMT

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)

Re: Total column in Script

alif — Thu, 28 Mar 2019 12:03:07 GMT

Thank you all.