Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis Sum with date restrictions

For example I have following table:

ID

ActiveDatePrice

1

1/1/201515
21/15/201513
34/8/201517
4

5/17/2015

16
58/8/201518
610/4/201518
710/15/201515
812/1/201513
91/3/201614
101/7/201619
113/4/201612
124/8/201612

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?

1 Solution

Accepted Solutions
sunny_talwar

Not sure, but may be this:

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

View solution in original post

7 Replies
sunny_talwar

Not sure, but may be this:

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

qlikviewwizard
Master II
Master II

Hi Sunny,

Why you used & in the formula?

tyagishaila
Specialist
Specialist

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

Not applicable
Author

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

Anil_Babu_Samineni

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.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

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

Capture.PNG

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

tamilarasu
Champion
Champion

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)