Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
arjanijlenhave
New Contributor II

Calculating a Sum with set analysis

 

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.

 

3 Replies
MVP & Luminary
MVP & Luminary

Re: Calculating a Sum with set analysis

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]))


talk is cheap, supply exceeds demand
arjanijlenhave
New Contributor II

Re: Calculating a Sum with set analysis

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

MVP & Luminary
MVP & Luminary

Re: Calculating a Sum with set analysis

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.


talk is cheap, supply exceeds demand