Expression to ignore rows in calculation based on two different dimension value

[alina_qlik]

Hello Experts ,

What can be put as expression for sum of Net earn wherein i can ignore such rows where Value of L5 is blank and L4 has multiple entries of similar record ?

L1	L2	L3	L4	L5	Net_Earn
Trade	Trade D	Trade d L1	CONSUMABLES		2,156
Trade	Trade D	Trade d L1	EFD		13,368
Trade	Trade D	Trade d L1	EFD	EDFO	8,092
Trade	Trade D	Trade d L1	EFD	LO	5,253
Trade	Trade D	Trade d L2	ESD	IO	6,860
Trade	Trade D	Trade d L2	ESD	SO	6,671
Trade	Trade D	Trade d L2	FDO	SSE	6,178
Trade	Trade D	Trade d L2	FPL		3,882
Trade	Trade D	Trade d L2	SIP		4,002
Trade	Trade D	Trade d L2	SIP	IPO	2,001
Trade	Trade D	Trade d L2	SIP	LLC	2,001

Thank You all !! 

[JGMDataAnalysis]

I think I have not understood your requirement very well but this is my possible resolution:

Script:

Chart Expression:

Sum({<[ID] -= {"=Aggr(Count({1} [L4]), [L4]) > 1 And Len(Trim([L5])) = 0"}>} [Net_Earn])

[alina_qlik]

Thanks for your reply , but the expression does not seem to work .. 

[sunny_talwar]

So, what you have shared is the input data? What would you want the output to look like? Both these rows to now show up?

[JGMDataAnalysis]

I interpreted that you needed to exclude the two records that I highlight in the screenshot since they are the only ones that simultaneously meet the two conditions you mention.

"What can be put as expression for sum of Net earn wherein i can ignore such rows where Value of L5 is blank and L4 has multiple entries of similar record?"

[alina_qlik]

Thank you for reaching out to my doubt .

I require something like this 

L1	L2	L3	L4	L5	Net_Earn
Trade	Trade D	Trade d L1	CONSUMABLES		2,156
Trade	Trade D	Trade d L1	EFD		13,368
Trade	Trade D	Trade d L1	EFD	EDFO	8,092
Trade	Trade D	Trade d L1	EFD	LO	5,253
Trade	Trade D	Trade d L2	ESD	IO	6,860
Trade	Trade D	Trade d L2	ESD	SO	6,671
Trade	Trade D	Trade d L2	FDO	SSE	6,178
Trade	Trade D	Trade d L2	FPL		3,882
Trade	Trade D	Trade d L2	SIP		4002
Trade	Trade D	Trade d L2	SIP	IPO	2,001
Trade	Trade D	Trade d L2	SIP	LLC	2,001

OLD Total	60,464
NEW Total	43,094

Any L4 record which do not have L5 data and has multiple entries should be omitted . 

Ex . one row of EFD does not haev any L5 and also there are multiple entries of L4  , with similar values in L1,L2,L3,L4 

Same goes for SIP as well . 

[alina_qlik]

********** multiple entries of EFD 

[JGMDataAnalysis · Accepted solution]

Something like this?

Test:
NOCONCATENATE
LOAD 
	RowNo() AS ID, 
    L1 & '|' & L2 & '|' & L3 & '|' & L4 AS CheckDup,
	*
;
LOAD * INLINE [
      L1,	 L2,	  L3,	L4,	  L5, 				 Net_Earn
      Trade, Trade D, Trade d L1, CONSUMABLES, ,     2156
      Trade, Trade D, Trade d L1, EFD,	 	   ,	 13368
      Trade, Trade D, Trade d L1, EFD,		   EDFO, 8092
      Trade, Trade D, Trade d L1, EFD,		   LO,	 5253
      Trade, Trade D, Trade d L2, ESD,		   IO,	 6860
      Trade, Trade D, Trade d L2, ESD,		   SO,	 6671
      Trade, Trade D, Trade d L2, FDO,		   SSE,	 6178
      Trade, Trade D, Trade d L2, FPL,	 	   ,	 3882
      Trade, Trade D, Trade d L2, SIP,	 	   ,	 4002
      Trade, Trade D, Trade d L2, SIP,		   IPO,	 2001
      Trade, Trade D, Trade d L2, SIP,		   LLC,	 2001
];

Sum({<[ID] -= {"=Aggr(Count({1} [CheckDup]), [CheckDup]) > 1 And Len(Trim([L5])) = 0"}>} [Net_Earn])

[JGMDataAnalysis]

Another alternative defining flags in script and avoiding the Aggr function.

SET HidePrefix = '_';

Test:
NOCONCATENATE
LOAD
	RowNo() AS ID,
    If(Len(Trim(L5)), 0, 1) AS _FlagEmptyL5,
    L1 & '|' & L2 & '|' & L3 & '|' & L4 AS CheckDupTemp,
	*
;
LOAD * INLINE [
      L1,	 L2,	  L3,	L4,	  L5, 				 Net_Earn
      Trade, Trade D, Trade d L1, CONSUMABLES, ,     2156
      Trade, Trade D, Trade d L1, EFD,	 	   ,	 13368
      Trade, Trade D, Trade d L1, EFD,		   EDFO, 8092
      Trade, Trade D, Trade d L1, EFD,		   LO,	 5253
      Trade, Trade D, Trade d L2, ESD,		   IO,	 6860
      Trade, Trade D, Trade d L2, ESD,		   SO,	 6671
      Trade, Trade D, Trade d L2, FDO,		   SSE,	 6178
      Trade, Trade D, Trade d L2, FPL,	 	   ,	 3882
      Trade, Trade D, Trade d L2, SIP,	 	   ,	 4002
      Trade, Trade D, Trade d L2, SIP,		   IPO,	 2001
      Trade, Trade D, Trade d L2, SIP,		   LLC,	 2001
];

LEFT JOIN (Test)
LOAD CheckDupTemp, If(Count(CheckDupTemp) > 1, 1, 0) AS _FlagDup 
RESIDENT Test GROUP BY CheckDupTemp;

DROP FIELD CheckDupTemp;

Sum({<[ID] -= {"=[_FlagEmptyL5] And [_FlagDup]"}>} [Net_Earn])