Hello QlikCommunity,
I'm having some issues setting a date range in the set analysis and could really use some help. We believe our issues are coming from the use of an input field(StartDate & EndDate) that is dynamically set by the user by location. We would like to analyze locations for different business divisions based upon the date range set by the user that typically corresponds to when a certain product mix was in stock. Each business division has it's own set of data so the date ranges/locations change and we reuse our .qvw for several business divisions and would like to expand to additional business divisions for which the locations and date ranges are currently unknown.
Here is what we have:
| Location | StartDate | EndDate |
|---|
A | 1/1/2013 | 3/20/2013 |
| B | 1/1/2013 | 5/30/2013 |
| C | 3/1/2013 | 5/30/2013 |
| D | 3/15/2013 | 6/15/2013 |
| E | 2/1/2013 | 3/20/2013 |
Here is what we have tried. These expressions have worked when all the start/end dates are the same, but when they are different they do not:
1) =Money(Sum({<[Snapshot Date]={'$(=(max([Snapshot Date])))'}, [Sales Date] = {">=$(=date(StartDate,'MM/DD/YYYY')) <=$(=date(EndDate,'MM/DD/YYYY'))"}>}
Sales), '$#,##0')
2) =Money(Sum({<[Snapshot Date]={'$(=(max(ALL [Snapshot Date])))'}, [Sales Date]={'>=$(StartDate)'}, [Salex Date]={'<=$(EndDate)'}>}
Sales), '$#,##0')
3) =Money(Sum({<[Snapshot Date]={'$(=(max([Snapshot Date])))'}, [Salex Date]={"=([Salex Date]>=StartDate)"}>*
<[Snapshot Date]={'$(=(max([Snapshot Date])))'}, [Salex Date]={"=([Discharge Date]<=EndDate)"}>}
Sales), '$#,##0')
We have purposefully avoided using if statements because of how we treat null values. Does anyone have any other ideas on how we could make this work in the set analysis?
Your thoughts are much appreciated!
Thanks, Alex
