topic Re: Rangesum with time criteria in QlikView

Rangesum with time criteria

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

Last week I posted a question regarding a range sum with varied requirements, of which I got the answer for (thank you!) How can I remove a dimension from a table when it is part of a calculated expression? and now they are wanting to limit the programs included by adding a time limit.

I need to include the Program IF there will also be a "Go-To-Stage Meeting Date" in the next two months.

I have been trying to figure it out, but I can only manually calculate if there is a meeting date in the next months by each stage (my example is in the attached file), but I can't figure out how to incorporate it into the existing RangeSum calculation. Below is sorta what I would like - I know, the syntax is totally wrong 😊

Is there an easy fix for this?

RangeSum(
Count({<[Go-To Seed List Done] = {'Yes'}, [Stage] = {'Seed'}, [Go-To-Seed Meeting Date] = between Today() and Today()+60}>}[Program]),
Count({<[Go-To-Alpha List Done] = {'Yes'}, [Stage] = {'Alpha'}>}[Program]), [Go-To-Alpha Meeting Date] = between Today() and Today()+60}>}[Program]),
Count({<[Go-To-Beta List Done] = {'Yes'}, [Stage] = {'Beta'}>}[Program]) [Go-To-Beta Meeting Date] = between Today() and Today()+60}>}[Program]),
)

Thanks again in advance!

Re: Rangesum with time criteria

sunny_talwar — Mon, 06 May 2019 12:46:19 GMT

Which of the two charts are we trying to fix here? What is the exact number you are hoping to see?

Re: Rangesum with time criteria

data_RN — Mon, 06 May 2019 13:38:39 GMT

Hi Sunny,

I am looking to see the number of programs that have completed their list for that stage AND have a meeting date in the the next 2 months for that stage. So something like this:

Group	Count (Program)	Combo - List done for current stage	List done for current stage AND has a meeting planned in next 2 months
	9	5	2
a	3	2	2
b	3	1	0
c	3	2	0

Is that clearer?

Thanks for your help!

Re: Rangesum with time criteria

sunny_talwar — Mon, 06 May 2019 13:55:58 GMT

Why can't you just use this same expression from the above chart in the below chart?

(Count(If([Go-To Seed Meeting Date] >Today(),0)))-(Count(If([Go-To Seed Meeting Date] >Today()+60,0)))

Re: Rangesum with time criteria

data_RN — Mon, 06 May 2019 14:39:04 GMT

Hello again,

It only gives me part of the answer. I need the sum total using the current stage. I have only figured out how to do it by one stage at a time.

Here is what I mean:

Group	Stage	Count (Program)	List Done for Current Stage	Meeting Dates within 60 days for Seed Stage Only	Meeting Dates within 60 days for Alpha Stage Only	Meeting Dates within 60 days for Beta Stage Only	Current Stage, List Done IN Time Period for Current stage - THIS IS WHAT I NEED
		9	5	2	2	1	2
a	Seed	1	1	1	0	0	1
a	Alpha	1	1	0	1	0	1
a	Seed	1		1	0	0	-
b	Beta	1		0	0	0	-
b	Seed	1	1	0	1	0	-
b	Alpha	1		0	0	0	-
c	Beta	1		0	0	0	-
c	Beta	1	1	0	0	0	-
c	Alpha	1	1	0	0	1	-

I updated the file with the above and attached. The last column is the one I can't figure out. I had hoped I could alter your original RangeSum formula to include the time period check for the Go-To- "X" Meeting Date, but I can't seem to get it to work 😕

Re: Rangesum with time criteria

sunny_talwar — Mon, 06 May 2019 14:47:57 GMT

I am still not 100% sure I understand, but try this

RangeSum( Count({<[Go-To Seed List Done] = {'Yes'}, [Stage] = {'Seed'}, [Go-To Seed Meeting Date] = {">=$(=Date(Today()))<=$(=Date(Today() + 60))"}>} [Program]), Count({<[Go-To-Alpha List Done] = {'Yes'}, [Stage] = {'Alpha'}, [Go-To-Alpha Meeting Date] = {">=$(=Date(Today()))<=$(=Date(Today() + 60))"}>}[Program]), Count({<[Go-To-Beta List Done] = {'Yes'}, [Stage] = {'Beta'}, [Go-To-Beta Meeting Date] = {">=$(=Date(Today()))<=$(=Date(Today() + 60))"}>}[Program]) )

Re: Rangesum with time criteria

data_RN — Mon, 06 May 2019 16:03:23 GMT

AWESOME! That is exactly it!

Thank you 🙂