Two Condition calculation

[Hamid_Reza_Rashidi]

Hi Everybody,

Please let me have your comments based on followings:

1. My table is as followings:

Date_S	Tech_No	QTY	Supplied_Per
13990910	12072	1890	0
13990910	11201	20	0
13990910	11202	10	0
13990910	10001	120	1
13990911	12072	1890	0
13990911	11201	20	0
13990911	11202	10	0
13990911	10001	120	1
13990912	12072	1890	0
13990912	11201	20	1
13990912	11202	10	1
13990912	10001	120	1
13990913	12072	1890	0
13990913	11201	20	1
13990913	11202	10	1
13990913	10001	120	1
13990915	12072	1890	0
13990915	11201	20	1
13990915	11202	10	1
13990915	10001	120	1
13990916	12072	1890	0
13990916	11201	20	1
13990916	11202	10	1
13990916	10001	120	1

2. I am going to have following calculation for :

Sum of QTY for items which their Date_s Is Max and Supplied_Per is 0

Best Wishes.

[MayilVahanan]

Hi @Hamid_Reza_Rashidi 

Try like below

Table1:
LOAD * INLINE [
Date_S, Tech_No, QTY, Supplied_Per
13990910, 12072, 1890, 0
13990910, 11201, 20, 0
13990910, 11202, 10, 0
13990910, 10001, 120, 1
13990911, 12072, 1890, 0
13990911, 11201, 20, 0
13990911, 11202, 10, 0
13990911, 10001, 120, 1
13990912, 12072, 1890, 0
13990912, 11201, 20, 1
13990912, 11202, 10, 1
13990912, 10001, 120, 1
13990913, 12072, 1890, 0
13990913, 11201, 20, 1
13990913, 11202, 10, 1
13990913, 10001, 120, 1
13990915, 12072, 1890, 0
13990915, 11201, 20, 1
13990915, 11202, 10, 1
13990915, 10001, 120, 1
13990916, 12072, 1890, 0
13990916, 11201, 20, 1
13990916, 11202, 10, 1
13990916, 10001, 120, 1
];

Load Tech_No, Max(Date_S) as MaxDate Resident Table1 where Supplied_Per = 0 Group by Tech_No;

Dim: Tech_No

Exp: Sum( {<Supplied_Per={0}>}If(Date_S = MaxDate, QTY))

[Hamid_Reza_Rashidi]

Hi, thanks, i am looking for conjunction column 0 and row 13990916 i.e 41000

Date_S	Tech_ID	QTY	Supply_Per
13990910	8972	36,000	0
13990910	101	5	0
13990910	10010	2	0	PIVOT TABLE
13990910	101	150	1	Sum of QTY	Supply_Per
13990911	8972	37,000	0	Date_S	0	1	Grand Total
13990911	112100101	6	0	13990910	36007	150	36157
13990911	100102	3	0	13990911	37009	150	37159
13990911	101	150	1	13990912	38000	161	38161
13990912	8972	38,000	0	13990913	39000	163	39163
13990912	100101	7	1	13990915	40000	165	40165
13990912	100102	4	1	13990916	41000	167	41167
13990912	101	150	1	Grand Total	231016	956	231972
13990913	8972	39,000	0
13990913	100101	8	1
13990913	100102	5	1
13990913	101	150	1
13990915	8972	40,000	0
13990915	100101	9	1
13990915	100102	6	1
13990915	101	150	1
13990916	8972	41,000	0
13990916	100101	10	1
13990916	100102	7	1
13990916	101	150	1

[Kushal_Chawda · Accepted solution]

@Hamid_Reza_Rashidi  try below expression

Sum({<Supply_Per={0},Date_S={"$(=max({<Supplied_Per={0}>}Date_S))"}>}QTY)

[Hamid_Reza_Rashidi]

thanks, it works good