topic Conditional Sum Question in QlikView

Conditional Sum Question

BrunPierre — Thu, 27 Jun 2019 17:05:18 GMT

ID	R_Type	R_Company	%_CONTRIBUTION
FAR	C	F0	0
FAR	C	F0	40
FAR	F	F5	0
FAR	F	F5	54.17
FAR	T	FS	0
FAR	T	SS	22

Hello guys!

With the above I'd like to perform a conditional s

For every R_Type , sum the %contribution based on the R_company, that is

IF R_Type = C and R_Company is unique (F0), then 0 + 40 = 40

IF R_Type = F and R_Company is unique (FS and SS), then 0 + 54.17 = 54.17

IF R_Type = T and R_Company is not unique (F5 & SS), then 0 + 22 *(2) = 44 because there are two distinct R_Company.

Thanks

Re: Conditional Sum Question

rubenmarin — Thu, 27 Jun 2019 18:41:46 GMT

Hi, you dont' say what happens when R_Type=C or F and company is not unique, or R_type=T and company is unique.

As a guess, can you try?:

Sum(TOTAL <ID, R_Type> %_CONTRIBUTION)*Count(DISTINCT TOTAL <ID, R_Type> R_Company)

Re: Conditional Sum Question

BrunPierre — Fri, 28 Jun 2019 09:50:29 GMT

Thanks for the quick response.

R_Type is a single value field i.e It does not contain F or C. But however when R_Company contains unique R_Company with different % contributions like below ;

FO 1.05

FO 1.5

the resultant should be F0 = 1.05 + 1.5 = 2.55

Could you kindly add this to your earlier expression, thanks.

Re: Conditional Sum Question

rubenmarin — Wed, 03 Jul 2019 08:51:03 GMT

Hi, what doesn't works using the expression I posted before?

Can you post a sample or expand your example?

Re: Conditional Sum Question

BrunPierre — Thu, 04 Jul 2019 11:49:18 GMT

Hi,

Attached is the sample as per requested. Hope it's self-explanatory.

Again, thank you.

Re: Conditional Sum Question

rubenmarin — Thu, 04 Jul 2019 12:42:08 GMT

Hi,

So, when ID=DMP it should be the same as default?

If(ID='FAR'
,Sum(TOTAL <ID, R_Type> %_CONTRIBUTION)*Count(DISTINCT TOTAL <ID, R_Type> R_Company)
,Sum(%_CONTRIBUTION)
)