Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Arjunarao
Honored Contributor 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

Re: Grouping the data on TransID & Dept

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;

1 Reply

Re: Grouping the data on TransID & Dept

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;