topic Re: Set analysis Sum with date restrictions in QlikView

Set analysis Sum with date restrictions

Wed, 25 Nov 2020 16:16:04 GMT

For example I have following table:

ID	ActiveDate	Price
1	1/1/2015	15
2	1/15/2015	13
3	4/8/2015	17
4	5/17/2015	16
5	8/8/2015	18
6	10/4/2015	18
7	10/15/2015	15
8	12/1/2015	13
9	1/3/2016	14
10	1/7/2016	19
11	3/4/2016	12
12	4/8/2016	12

And I set the EndDate is 6/1/2016. The EndDate must be a vriable that can be changed later.

I want to sum the prices if the ActiveDate is 1 month before the end date, 2 months before the end date, 3 months before the end date etc...

I use the set analysis for expression: sum the prices if the ActiveDate is 1 month before the end date

sum({<addmonths(ActiveDate,1) = {">=$(EndDate)"}>} Price)

This is not recoganized by QlikView. I get ' - ' as result.

Can anyone help me to fix my expression?

Or is there any other better method?

Re: Set analysis Sum with date restrictions

sunny_talwar — Mon, 27 Jun 2016 22:58:23 GMT

Not sure, but may be this:

Sum({<ActiveDate = {"$(='>=' & Date(AddMonths($(EndDate), -1)))"}>} Price)

Re: Set analysis Sum with date restrictions

qlikviewwizard — Tue, 28 Jun 2016 02:54:52 GMT

Hi Sunny,

Why you used & in the formula?

Re: Set analysis Sum with date restrictions

tyagishaila — Tue, 28 Jun 2016 05:50:16 GMT

Sum({<ActiveDate = {"=Month(ActiveDate)=Month(EndDate)-1"}>} Price)

Re: Set analysis Sum with date restrictions

Tue, 28 Jun 2016 15:37:31 GMT

Thank you so much! It works! If you have time, can you explain about the structure of the expression?

Re: Set analysis Sum with date restrictions

Anil_Babu_Samineni — Tue, 28 Jun 2016 17:57:58 GMT

Ivy,

Sum({<ActiveDate = {"$(='>=' & Date(AddMonths($(EndDate), -1)))"}>} Price)

Sum(Price) -- Total Price

Sum({<>}Price) - Syntax of Set Analysis

Sum({<ActivateDate = >}Price) - Expected Output depends on Field

Date(AddMonths($(EndDate), -1)) - You know what is this, It indicates EndDate is the variable which initiate the date. So, Here "AddMonths" we just add some months of EndDate - 1

>= - Indicates, Get the above of data which coming from Date(AddMonths($(EndDate), -1))

- Might be helpful.

Re: Set analysis Sum with date restrictions

sunny_talwar — Thu, 30 Jun 2016 01:18:34 GMT

I think the explanation might be needed for the set analysis part here

Sum({<ActiveDate = {"$(='>=' & Date(AddMonths($(EndDate), -1)))"}>} Price)

Basically to understand what the part in red is doing, put the part within the dollar sign expansion $() into a text box object

='>=' & Date(AddMonths($(EndDate), -1))

Basically, we are using the date entered in the calendar object (and saved to EndDate) and moving it a month back and concatenated '>=' at the beginning. Dollar sign expansion converts the expression into this -> >=2/1/2016, which is nothing but telling the expression to include everything which is above the one month before the selected date.

Does this clarify the expression?

Best,

Sunny

Re: Set analysis Sum with date restrictions

tamilarasu — Wed, 13 Jul 2016 02:45:43 GMT

Hi qlikviewwizard,

Sunny has already explained the reason in below trail. I just want to share same expression but without & symbol.

Sum({<ActiveDate = {">=$(=Date(AddMonths($(EndDate), -1)))"}>} Price)