topic Re: SUM IF question - using InQuarter in QlikView

SUM IF question - using InQuarter

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

Hello All,

I hope I have an easy one for you 🙂 I am hoping I am simply overthinking it...

I am trying to get a sum of the amount of products that have a launch date this quarter. I have been able to flag the products as the dates being in the is quarter using:

InQuarter([Launch Date],Today(),0)

Product Group	Product	Launch Date	This Quarter
			-4
Fruit	apples	12/31/2018	0
Fruit	bananas	1/15/2019	0
Vegetables	beet	4/20/2019	-1
Vegetables	carrot	4/1/2019	-1
Fruit	oranges	5/1/2019	-1
Vegetables	turnip	5/25/2019	-1

But what I really want is to show the number of Products Launching THIS quarter by Product group. I know this is wrong, but something like this:

=SUM(if([Launch Date] = InQuarter,Today(),[Product Group]))

Product Group	This Quarter
Total	4
Fruit	1
Vegetables	3

Thanks in advance!

Re: SUM IF question - using InQuarter

marcus_sommer — Fri, 03 May 2019 12:02:24 GMT

If you have this flag within the data you could use something like:

count({< [This Quarter] = {-1}>} Product)

- Marcus

Re: SUM IF question - using InQuarter

sunny_talwar — Fri, 03 May 2019 12:06:13 GMT

If you don't have a flag, than may be this

Count({<[Launch Date] = {"=InQuarter([Launch Date],Today(),0)"}>}[Launch Date])

Re: SUM IF question - using InQuarter

Anonymous — Fri, 03 May 2019 12:20:19 GMT

use a straight table with Dimension "Product Group" and Expression:

aggr(sum(if (InQuarter([Launch Date],Today(),0)=-1,1)),[Product Group])

and Show total and you get your desired output

Re: SUM IF question - using InQuarter

data_RN — Fri, 03 May 2019 13:17:44 GMT

Thanks Marcus,

I get an error - "Error in set modifier ad hoc element list".

Attached is a screenshot.

'This Quarter' is using: InQuarter([Launch Date],Today(),0)

Is there something wrong with the 'This Quarter' expression that is creating the error?

Re: SUM IF question - using InQuarter

data_RN — Fri, 03 May 2019 13:22:40 GMT

Thank you Sunny!

Indeed that works perfectly! I had tried SO many iterations of the syntax and I was completely missing the "" in the brackets.

Cheers and Happy Friday!

Re: SUM IF question - using InQuarter

data_RN — Fri, 03 May 2019 13:28:46 GMT

Thank you Hrlinder!

Indeed this works too and the syntax isn't as complicated.

Cheers!