Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Grouping data by month and person

Looking for some help here

I have a table of sales data per day by employee like so:

Employee IDDateNumber sold
110/08/20143
111/0820145
216/08/20146
219/09/201412
221/09/20142

Which I want to convert to a table in the following form:

Employee IDMonthTotal sold
1August8
2August6
2September14

Basically, total sold by employee for each month. This is probably really easy and I am too tired to work it out, but any help would be much appreciated

5 Replies
MVP
MVP

Re: Grouping data by month and person

make a chart (straight table)

with 2 dimensions

Employee ID

=year(Date) & '-' & Month(Date)

and the expression

sum(Sales)

Not applicable

Re: Grouping data by month and person

Sorry if this is obvious, but how would I do this in the loadscript?

Re: Grouping data by month and person

table1:

LOAD [Employee ID],

    Date,

    [Number sold]

FROM

[http://community.qlik.com/thread/129712]

(html, codepage is 1252, embedded labels, table is @1);

table2:

LOAD [Employee ID],

    Month(Date) as Month,

    Sum([Number sold]) as [Total sold]

Resident table1

Group By [Employee ID],Month(Date);

hope this helps

regards

Marco

Not applicable

Re: Grouping data by month and person

Hi,

I have created a resident table based on your example. I performed aggregation on that table..

tempTable:  //this table is to emulate the same data set

load * inline [

Employee_ID, Date_Sold, Number_Sold

1, 10/08/2014, 3

1, 11/08/2014, 5

2, 16/08/2014, 6

2, 19/09/2014, 12

2, 21/09/2014, 2

];

finalTable: //perfoming aggregation

load

Employee_ID as Emp_ID,

month(date#(Date_Sold,'DD/MM/YYYY')) as On_Date_Sold,

sum(Number_Sold) as Total_Sale

Resident tempTable

group by

Employee_ID, month(date#(Date_Sold,'DD/MM/YYYY'))

order by 1, 2;


I finally put the columns of the above finalTable in QV's Table Box object.QV_total_sale.PNG.png

Re: Re: Grouping data by month and person

QlikCommunity_Thread_129712_Pic2.JPG.jpg

if you add the missing slash in your sample table for 11/08/2014, this script should deliver the specified output

QlikCommunity_Thread_129712_Pic1.JPG.jpg

QlikCommunity_Thread_129712_Pic3.JPG.jpg

hope this helps

regards

Marco

Community Browser