topic Re: Sum With Condition in QlikView

Sum With Condition

Chanty4u — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I want exclude only actual value for max quarter

in the attached app i have mentioned the requirment and result too,

Re: Sum With Condition

Anil_Babu_Samineni — Tue, 24 Jul 2018 07:07:33 GMT

How about this?

Sum({<Quarter={"$(=MaxString(Quarter))"}>} Forecast)

Re: Sum With Condition

Chanty4u — Tue, 24 Jul 2018 07:12:54 GMT

No anil. its showing only for max sum forecast i need both selected means

max quarter forecast and other selected actual

Re: Sum With Condition

qlikviewwizard — Tue, 24 Jul 2018 07:20:45 GMT

Hi Chanty 4u

Try like this.

=max({<Quarter={$(=concat(chr(39) & Quarter & chr(39),',') )}>}Actuals)+max({<Quarter={$(=concat(chr(39) & Quarter & chr(39),',') )}>}Forecast)

Re: Sum With Condition

tresB — Tue, 24 Jul 2018 07:22:16 GMT

This?

RangeSum(Sum({<Quarter={'$(=maxstring(Quarter))'}>}Forecast),Sum({<Quarter-={'$(=maxstring(Quarter))'}>}Actuals))

Re: Sum With Condition

Chanty4u — Tue, 24 Jul 2018 07:58:19 GMT

thanks Tresesco B

this is my original expression how can i incorporate in this?

if(Quarter=vMaxQtr and otherQtr Selection ,$(vFc)+$(vAc))

Re: Sum With Condition

tresB — Tue, 24 Jul 2018 08:28:00 GMT

You might have to create two new variables for this, because you need to include set analysis in it.

Re: Sum With Condition

raviityou — Tue, 24 Jul 2018 12:00:40 GMT

please find attached file,

Re: Sum With Condition

Chanty4u — Tue, 24 Jul 2018 13:03:31 GMT

this is my original expression how can i incorporate in this?

here how can i use and condition in if ?

=IF(Quarter = vNewMaxQtr,(($(vFCYOYServ)-$(vACTYOYServ)) / $(vACTYOYServ))

if(Quarter =$(vQtr) and Quarter=vNewMaxQtr, ($(vFCAMultiple)- $(vACTYOYServ))/$(vACTYOYServ),(($(vCurACTYOYServ)-$(vACTYOYServ)) / $(vACTYOYServ))) )

second if i not working

vQtr = Quarter field only

vNewMaxQutr = 2018Q3

Re: Sum With Condition

pintucs20 — Fri, 27 Jul 2018 10:59:35 GMT

Hi ,

can you share excel sheet of data.