topic Re: Summing with if conditions in QlikView

Summing with if conditions

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

Hello all,

I was hoping someone would help me with a challenge I am facing.

I am trying to sum ActualVolume for Where _Calendar_RollMonthSort is not 13 and CalendarYear is not 2019 and right(Calendar_RollMonthName,1) is not 'A'.

Example If([_Calendar_RollMonthSort] <> '13' AND CalendarYear <> 2019 AND right(Calendar_RollMonthName,1) <> 'A', SUM({<CalendarCurrRolling13Mo={1}>}ActualVolume)/1000)

However it is not working as planned as it looks at [_Calendar_RollMonthSort] <> '13' and excludes both records. Then the next part "CalendarYear <> 2019 " excluded all with the year 2019 ect.

I need it to only exclude if all conditions are true.

Your help would be appreciated

Re: Summing with if conditions

darrinturner — Fri, 14 Aug 2020 22:23:11 GMT

I have also tried using: SUM({<CalendarCurrRolling13Mo={1}, _Calendar_RollMonthSort] -= {'13'}, CalendarYear -= {'2019'} , Right([_Calendar_RollMonthName],1) -= {'A'}>} _ActualVolume)/1000

With no luck

Re: Summing with if conditions

Kushal_Chawda — Fri, 14 Aug 2020 22:35:40 GMT

try below

SUM({<CalendarCurrRolling13Mo={1},[_Calendar_RollMonthSort]-={'13'},CalendarYear-={2019} , Calendar_RollMonthName={"=right(Calendar_RollMonthName,1) <> 'A'"}>}ActualVolume)/1000

Re: Summing with if conditions

darrinturner — Fri, 14 Aug 2020 22:50:31 GMT

Kush, thank you for the code. However I loos records I don't want to lose.

In the image below, I have highlighted the record I actually want to exclude:

Re: Summing with if conditions

Kushal_Chawda — Sun, 16 Aug 2020 18:26:08 GMT

Would you be able to share sample?