Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Master II
Master II

Grouping the data on TransID & Dept

Hi All,

I want to group the data on TransID & Department.

My Data is like this for TRSID1 & DEPT_A:

MonthYearYearMonthSalesTrans_IdDepartment
220142014-023.5TRSID1DEPT_A
320142014-035.6TRSID1DEPT_A
420142014-047TRSID1DEPT_A
520142014-055.6TRSID1DEPT_A
620142014-066.8TRSID1DEPT_A
720142014-070.4TRSID1DEPT_A
820142014-087TRSID1DEPT_A
920142014-092.1TRSID1DEPT_A
1020142014-104.4TRSID1DEPT_A
1120142014-114.4TRSID1DEPT_A
1220142014-122.1TRSID1DEPT_A
120152015-013TRSID1DEPT_A
220152015-022.2TRSID1DEPT_A
320152015-031.7TRSID1DEPT_A
420152015-041.7TRSID1DEPT_A
520152015-051.8TRSID1DEPT_A
620152015-063.3TRSID1DEPT_A
720152015-073.7TRSID1DEPT_A
820152015-083.7TRSID1DEPT_A
920152015-098.1TRSID1DEPT_A
1020152015-1013.4TRSID1DEPT_A

I want to show the output of Sales zero for those which the time is more than one year.

So out should be  like this.

MonthYearYearMonthSalesTrans_IdDepartment
220142014-023.5TRSID1DEPT_A
320142014-035.6TRSID1DEPT_A
420142014-047TRSID1DEPT_A
520142014-055.6TRSID1DEPT_A
620142014-066.8TRSID1DEPT_A
720142014-070.4TRSID1DEPT_A
820142014-087TRSID1DEPT_A
920142014-092.1TRSID1DEPT_A
1020142014-104.4TRSID1DEPT_A
1120142014-114.4TRSID1DEPT_A
1220142014-122.1TRSID1DEPT_A
120152015-013TRSID1DEPT_A
220152015-022.2TRSID1DEPT_A
320152015-030TRSID1DEPT_A
420152015-040TRSID1DEPT_A
520152015-050TRSID1DEPT_A
620152015-060TRSID1DEPT_A
720152015-070TRSID1DEPT_A
820152015-080TRSID1DEPT_A
920152015-090TRSID1DEPT_A
1020152015-100TRSID1DEPT_A

Please help me.

Thanks in advance.

1 Solution

Accepted Solutions
Highlighted

May be this expression:

If(YearMonthNum <= Date(AddYears(Min(TOTAL <Department, Trans_Id> YearMonthNum), 1), 'YYYY-MM'), sum(Sales), 0)

Capture.PNG

Had to create YearMonthNum in the script because your YearMonth field was not a date field. Script (addition in red):

Data:

LOAD Month,

     Year,

     YearMonth,

     Sales,

     Trans_Id,

     Department

FROM

Data_Sales.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join (Data)

Add Only

LOAD *,

  Date#(YearMonth, 'YYYY-MM') as YearMonthNum

Resident Data;

View solution in original post

1 Reply
Highlighted

May be this expression:

If(YearMonthNum <= Date(AddYears(Min(TOTAL <Department, Trans_Id> YearMonthNum), 1), 'YYYY-MM'), sum(Sales), 0)

Capture.PNG

Had to create YearMonthNum in the script because your YearMonth field was not a date field. Script (addition in red):

Data:

LOAD Month,

     Year,

     YearMonth,

     Sales,

     Trans_Id,

     Department

FROM

Data_Sales.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join (Data)

Add Only

LOAD *,

  Date#(YearMonth, 'YYYY-MM') as YearMonthNum

Resident Data;

View solution in original post