topic Re: Grouping the data on TransID & Dept in QlikView

topic Re: Grouping the data on TransID & Dept in QlikView https://community.qlik.com/t5/QlikView/Grouping-the-data-on-TransID-Dept/m-p/938716#M960484 <HTML><HEAD></HEAD><BODY><P>May be this expression:</P><P></P><P><STRONG>If(YearMonthNum <= Date(AddYears(Min(TOTAL <Department, Trans_Id> YearMonthNum), 1), 'YYYY-MM'), sum(Sales), 0)</STRONG></P><P></P><P><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/102696_Capture.PNG" style="height: auto;" /></P><P></P><P>Had to create YearMonthNum in the script because your YearMonth field was not a date field. Script (addition in red):</P><P></P><P><STRONG>Data:</STRONG></P><P><STRONG>LOAD Month, </STRONG></P><P><STRONG>     Year, </STRONG></P><P><STRONG>     YearMonth, </STRONG></P><P><STRONG>     Sales, </STRONG></P><P><STRONG>     Trans_Id, </STRONG></P><P><STRONG>     Department</STRONG></P><P><STRONG>FROM</STRONG></P><P><STRONG>Data_Sales.xlsx</STRONG></P><P><STRONG>(ooxml, embedded labels, table is Sheet1);</STRONG></P><P></P><P><STRONG style="color: #ff0000;">Left Join (Data)</STRONG></P><P><SPAN style="color: #ff0000;"><STRONG>Add Only</STRONG></SPAN></P><P><SPAN style="color: #ff0000;"><STRONG>LOAD *,</STRONG></SPAN></P><P><SPAN style="color: #ff0000;"><STRONG>  Date#(YearMonth, 'YYYY-MM') as YearMonthNum</STRONG></SPAN></P><P><STRONG style="color: #ff0000;">Resident Data;</STRONG></P></BODY></HTML> Wed, 21 Oct 2015 15:12:49 GMT sunny_talwar 2015-10-21T15:12:49Z Grouping the data on TransID & Dept https://community.qlik.com/t5/QlikView/Grouping-the-data-on-TransID-Dept/m-p/938715#M960483 <HTML><HEAD></HEAD><BODY><P>Hi All,</P><P>I want to group the data on TransID & Department.</P><P>My Data is like this for TRSID1 & DEPT_A:</P><P></P><P></P><TABLE border="0" cellpadding="0" cellspacing="0" width="384"><TBODY><TR><TD class="xl65" height="20" width="64">Month</TD><TD class="xl65" style="border-left: none;" width="64">Year</TD><TD class="xl65" style="border-left: none;" width="64">YearMonth</TD><TD class="xl65" style="border-left: none;" width="64">Sales</TD><TD class="xl65" style="border-left: none;" width="64">Trans_Id</TD><TD class="xl65" style="border-left: none;" width="64">Department</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">2</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-02</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">3.5</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">3</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-03</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">5.6</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">4</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-04</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">7</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">5</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-05</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">5.6</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">6</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-06</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">6.8</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">7</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-07</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0.4</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">8</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-08</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">7</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">9</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-09</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2.1</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">10</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-10</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">4.4</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">11</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-11</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">4.4</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">12</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-12</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2.1</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">1</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2015</TD><TD class="xl65" style="border-top: none; border-left: none;">2015-01</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">3</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">2</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2015</TD><TD class="xl65" style="border-top: none; border-left: none;">2015-02</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2.2</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">3</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2015</TD><TD class="xl65" style="border-top: none; border-left: none;">2015-03</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">1.7</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">4</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2015</TD><TD class="xl65" style="border-top: none; border-left: none;">2015-04</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">1.7</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">5</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2015</TD><TD class="xl65" style="border-top: none; border-left: none;">2015-05</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">1.8</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">6</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2015</TD><TD class="xl65" style="border-top: none; border-left: none;">2015-06</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">3.3</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">7</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2015</TD><TD class="xl65" style="border-top: none; border-left: none;">2015-07</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">3.7</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">8</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2015</TD><TD class="xl65" style="border-top: none; border-left: none;">2015-08</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">3.7</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">9</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2015</TD><TD class="xl65" style="border-top: none; border-left: none;">2015-09</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">8.1</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">10</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2015</TD><TD class="xl65" style="border-top: none; border-left: none;">2015-10</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">13.4</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR></TBODY></TABLE><P></P><P><SPAN style="color: #000080;"><EM><STRONG>I want to show the output of Sales zero for those which the time is more than one year.</STRONG></EM></SPAN></P><P>So out should be  like this.</P><P></P><P></P><TABLE border="0" cellpadding="0" cellspacing="0" style="width: 384px;"><TBODY><TR><TD class="xl65" height="20" width="64">Month</TD><TD class="xl65" style="border-left: none;" width="64">Year</TD><TD class="xl65" style="border-left: none;" width="64">YearMonth</TD><TD class="xl65" style="border-left: none;" width="64">Sales</TD><TD class="xl65" style="border-left: none;" width="64">Trans_Id</TD><TD class="xl65" style="border-left: none;" width="64">Department</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">2</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-02</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">3.5</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">3</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-03</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">5.6</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">4</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-04</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">7</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">5</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-05</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">5.6</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">6</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-06</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">6.8</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">7</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-07</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0.4</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">8</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-08</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">7</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">9</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-09</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2.1</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">10</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-10</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">4.4</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">11</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-11</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">4.4</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">12</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2014</TD><TD class="xl65" style="border-top: none; border-left: none;">2014-12</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2.1</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">1</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2015</TD><TD class="xl65" style="border-top: none; border-left: none;">2015-01</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">3</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;">2</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2015</TD><TD class="xl65" style="border-top: none; border-left: none;">2015-02</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">2.2</TD><TD class="xl65" style="border-top: none; border-left: none;">TRSID1</TD><TD class="xl65" style="border-top: none; border-left: none;">DEPT_A</TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;"><SPAN style="color: #000080;">3</SPAN></TD><TD align="right" class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">2015</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">2015-03</SPAN></TD><TD align="right" class="xl66" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">0</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">TRSID1</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">DEPT_A</SPAN></TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;"><SPAN style="color: #000080;">4</SPAN></TD><TD align="right" class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">2015</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">2015-04</SPAN></TD><TD align="right" class="xl66" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">0</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">TRSID1</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">DEPT_A</SPAN></TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;"><SPAN style="color: #000080;">5</SPAN></TD><TD align="right" class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">2015</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">2015-05</SPAN></TD><TD align="right" class="xl66" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">0</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">TRSID1</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">DEPT_A</SPAN></TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;"><SPAN style="color: #000080;">6</SPAN></TD><TD align="right" class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">2015</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">2015-06</SPAN></TD><TD align="right" class="xl66" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">0</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">TRSID1</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">DEPT_A</SPAN></TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;"><SPAN style="color: #000080;">7</SPAN></TD><TD align="right" class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">2015</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">2015-07</SPAN></TD><TD align="right" class="xl66" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">0</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">TRSID1</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">DEPT_A</SPAN></TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;"><SPAN style="color: #000080;">8</SPAN></TD><TD align="right" class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">2015</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">2015-08</SPAN></TD><TD align="right" class="xl66" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">0</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">TRSID1</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">DEPT_A</SPAN></TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;"><SPAN style="color: #000080;">9</SPAN></TD><TD align="right" class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">2015</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">2015-09</SPAN></TD><TD align="right" class="xl66" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">0</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">TRSID1</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">DEPT_A</SPAN></TD></TR><TR><TD align="right" class="xl65" height="20" style="border-top: none;"><SPAN style="color: #000080;">10</SPAN></TD><TD align="right" class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">2015</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">2015-10</SPAN></TD><TD align="right" class="xl66" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">0</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">TRSID1</SPAN></TD><TD class="xl65" style="border-top: none; border-left: none;"><SPAN style="color: #000080;">DEPT_A</SPAN></TD></TR></TBODY></TABLE><P></P><P></P><P>Please help me.</P><P></P><P>Thanks in advance.</P></BODY></HTML> Wed, 21 Oct 2015 14:44:14 GMT https://community.qlik.com/t5/QlikView/Grouping-the-data-on-TransID-Dept/m-p/938715#M960483 qlikviewwizard 2015-10-21T14:44:14Z Re: Grouping the data on TransID & Dept https://community.qlik.com/t5/QlikView/Grouping-the-data-on-TransID-Dept/m-p/938716#M960484 <HTML><HEAD></HEAD><BODY><P>May be this expression:</P><P></P><P><STRONG>If(YearMonthNum <= Date(AddYears(Min(TOTAL <Department, Trans_Id> YearMonthNum), 1), 'YYYY-MM'), sum(Sales), 0)</STRONG></P><P></P><P><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/102696_Capture.PNG" style="height: auto;" /></P><P></P><P>Had to create YearMonthNum in the script because your YearMonth field was not a date field. Script (addition in red):</P><P></P><P><STRONG>Data:</STRONG></P><P><STRONG>LOAD Month, </STRONG></P><P><STRONG>     Year, </STRONG></P><P><STRONG>     YearMonth, </STRONG></P><P><STRONG>     Sales, </STRONG></P><P><STRONG>     Trans_Id, </STRONG></P><P><STRONG>     Department</STRONG></P><P><STRONG>FROM</STRONG></P><P><STRONG>Data_Sales.xlsx</STRONG></P><P><STRONG>(ooxml, embedded labels, table is Sheet1);</STRONG></P><P></P><P><STRONG style="color: #ff0000;">Left Join (Data)</STRONG></P><P><SPAN style="color: #ff0000;"><STRONG>Add Only</STRONG></SPAN></P><P><SPAN style="color: #ff0000;"><STRONG>LOAD *,</STRONG></SPAN></P><P><SPAN style="color: #ff0000;"><STRONG>  Date#(YearMonth, 'YYYY-MM') as YearMonthNum</STRONG></SPAN></P><P><STRONG style="color: #ff0000;">Resident Data;</STRONG></P></BODY></HTML> Wed, 21 Oct 2015 15:12:49 GMT https://community.qlik.com/t5/QlikView/Grouping-the-data-on-TransID-Dept/m-p/938716#M960484 sunny_talwar 2015-10-21T15:12:49Z