topic Re: Load sum group by in QlikView

Load sum group by

ecabanas — Tue, 15 Mar 2016 16:25:40 GMT

Hi,

I have a table like this:

created_at	Custaccount	total
04/01/2016 10:11	C000003	79,55
18/01/2016 11:00	C000003	103,42
08/02/2016 11:08	C000003	137,77
23/02/2016 15:36	C000003	133,43
01/03/2016 12:34	C000003	74,63
11/03/2016 12:07	C000003	64,46
11/03/2016 19:36	C000003	17,95

I want to sum the total per month and group by custaccount in load script.

for example:

C00003 jan-16: 182,97€

C00003 feb-16: 271,20€

Many thank's

Eduard

Re: Load sum group by

sunny_talwar — Tue, 15 Mar 2016 16:29:17 GMT

Try something like this:

Table:

LOAD created_at,

MonthName(created_at) as MonthYear,

Custaccount,

total

FROM

test_file.xls

(biff, embedded labels, table is Sheet1$);

Join (Table)

LOAD MonthYear,

Sum(total) as [Sum of Total]

Resident Table

Group By MonthYear;

Re: Load sum group by

ecabanas — Tue, 15 Mar 2016 17:08:32 GMT

Hi Sunny

The totals did not sum correctly, see attached the project and the excel file

Many thank's

Eduard

Re: Load sum group by

sunny_talwar — Tue, 15 Mar 2016 17:11:30 GMT

May be you want it grouped by customer account and MonthYear:

Join([Customer_sales])

Load MonthYear,

Custaccount,

Sum(total) as Gasto

Resident [Customer_sales]

Group by MonthYear, Custaccount;

Re: Load sum group by

ecabanas — Tue, 15 Mar 2016 17:16:21 GMT

Yessss perfect!!! many thank's again Sunny....i have another for you see next post 🙂

Re: Load sum group by

jagathi — Thu, 25 Jul 2019 08:55:56 GMT

How do we use Where clause for this case?

Say we have loaded the input

get the sum of sales based on group by customer and month year

After this, I need to see the sales details which are more than 20,000 only

Then how do I apply the Where clause sum(sales)>20,000 for this script