topic Re: Calculating a Sum with set analysis in QlikView

Calculating a Sum with set analysis

Anonymous — Mon, 18 Jan 2016 14:39:52 GMT

Hello Community,

Please help us in solving a problem.

Let’s consider the following table below:

Object Key	Object version	Validity start date	Validity end date	Yearly premium
ABC	1	1-1-2015	1-3-2015	1000
ABC	2	1-3-2015	1-8-2015	1100
ABC	3	1-8-2015		1200
DEF	1	1-1-2015		2000
HIJ	1	1-1-2015	1-4-2015	1500
HIJ	2	1-4-2015	18-7-2015	1600

In our project we need to calculate the total yearly premium, based on a maximum date in a user based time selection. For illustrating purposes we have created 4 use cases with the desired outcome of the algorithm:

QlikView time selection 1	1-1-2015 up and including 31-3-2015
	Answer	Justification of answer
Total yearly premium	4600	= 1100 + 2000 + 1500
QlikView time selection 2	1-4-2015 up and including 31-12-2015
	Answer	Justification of answer
Total yearly premium	3200	= 1200 + 2000
QlikView time selection 3	1-6-2015
	Answer	Justification of answer
Total yearly premium	4700	= 1100 + 2000 + 1600
QlikView time selection 4	1-12-2015 up and including 31-12-2015
	Answer	Justification of answer
Total yearly premium	3200	= 1200 + 2000

We are quite sure we need to work with set analysis here, but we don’t know how to formulate the expression. Your input here is welcome.

Kind regards,

Arjan IJlenhave.

Re: Calculating a Sum with set analysis

Gysbert_Wassenaar — Mon, 18 Jan 2016 17:37:16 GMT

You can't use set analysis. I assume you have an independent date field somewhere to make selections in or a variable that gets its value from a calendar object. So try these:

variable vMaxDate: =num(max(MyDate)) //or gets its value from a calendar object

expression: sum( if([Validity start date] <= $(vMaxDate) and [Validity end date] >$(vMaxDate),[Yearly premium]))

Re: Calculating a Sum with set analysis

Anonymous — Tue, 19 Jan 2016 14:03:42 GMT

Hello Gysbert,

Thanks for your reply. I small comment/question here...

The yearly premium is directly related to an object key. In our project an object key can have multiple versions, but in the end it's still the same object.

In your proposed expression we don't see the variable 'object key'. Did you intend it like this?

Kind regards,

Arjan

Re: Calculating a Sum with set analysis

Gysbert_Wassenaar — Tue, 19 Jan 2016 15:47:16 GMT

Object key can be used as a chart dimension. If you put the expression in a text box then it'll work without a dimension.