Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Not sure, but may be this:
Sum({<ActiveDate = {"$(='>=' & Date(AddMonths($(EndDate), -1)))"}>} Price)
Not sure, but may be this:
Sum({<ActiveDate = {"$(='>=' & Date(AddMonths($(EndDate), -1)))"}>} Price)
Hi Sunny,
Why you used & in the formula?
Sum({<ActiveDate = {"=Month(ActiveDate)=Month(EndDate)-1"}>} Price)
Thank you so much! It works! If you have time, can you explain about the structure of the expression?
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.
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
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)