Grouping the data on TransID & Dept

[qlikviewwizard]

Hi All,

I want to group the data on TransID & Department.

My Data is like this for TRSID1 & DEPT_A:

Month	Year	YearMonth	Sales	Trans_Id	Department
2	2014	2014-02	3.5	TRSID1	DEPT_A
3	2014	2014-03	5.6	TRSID1	DEPT_A
4	2014	2014-04	7	TRSID1	DEPT_A
5	2014	2014-05	5.6	TRSID1	DEPT_A
6	2014	2014-06	6.8	TRSID1	DEPT_A
7	2014	2014-07	0.4	TRSID1	DEPT_A
8	2014	2014-08	7	TRSID1	DEPT_A
9	2014	2014-09	2.1	TRSID1	DEPT_A
10	2014	2014-10	4.4	TRSID1	DEPT_A
11	2014	2014-11	4.4	TRSID1	DEPT_A
12	2014	2014-12	2.1	TRSID1	DEPT_A
1	2015	2015-01	3	TRSID1	DEPT_A
2	2015	2015-02	2.2	TRSID1	DEPT_A
3	2015	2015-03	1.7	TRSID1	DEPT_A
4	2015	2015-04	1.7	TRSID1	DEPT_A
5	2015	2015-05	1.8	TRSID1	DEPT_A
6	2015	2015-06	3.3	TRSID1	DEPT_A
7	2015	2015-07	3.7	TRSID1	DEPT_A
8	2015	2015-08	3.7	TRSID1	DEPT_A
9	2015	2015-09	8.1	TRSID1	DEPT_A
10	2015	2015-10	13.4	TRSID1	DEPT_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.

Month	Year	YearMonth	Sales	Trans_Id	Department
2	2014	2014-02	3.5	TRSID1	DEPT_A
3	2014	2014-03	5.6	TRSID1	DEPT_A
4	2014	2014-04	7	TRSID1	DEPT_A
5	2014	2014-05	5.6	TRSID1	DEPT_A
6	2014	2014-06	6.8	TRSID1	DEPT_A
7	2014	2014-07	0.4	TRSID1	DEPT_A
8	2014	2014-08	7	TRSID1	DEPT_A
9	2014	2014-09	2.1	TRSID1	DEPT_A
10	2014	2014-10	4.4	TRSID1	DEPT_A
11	2014	2014-11	4.4	TRSID1	DEPT_A
12	2014	2014-12	2.1	TRSID1	DEPT_A
1	2015	2015-01	3	TRSID1	DEPT_A
2	2015	2015-02	2.2	TRSID1	DEPT_A
3	2015	2015-03	0	TRSID1	DEPT_A
4	2015	2015-04	0	TRSID1	DEPT_A
5	2015	2015-05	0	TRSID1	DEPT_A
6	2015	2015-06	0	TRSID1	DEPT_A
7	2015	2015-07	0	TRSID1	DEPT_A
8	2015	2015-08	0	TRSID1	DEPT_A
9	2015	2015-09	0	TRSID1	DEPT_A
10	2015	2015-10	0	TRSID1	DEPT_A

Please help me.

Thanks in advance.

[sunny_talwar · Accepted solution]

May be this expression:

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

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;