Using input fields to select date range in set analysis

Wed, 22 Jan 2014 15:37:05 GMT

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

Re: Using input fields to select date range in set analysis

Wed, 22 Jan 2014 17:15:08 GMT

The 1) expression looks good to me. Can you remove the Snapshot Date filter and just keep the Sales Date filter for testing

Re: Using input fields to select date range in set analysis

Wed, 22 Jan 2014 17:57:41 GMT

Hey Angad,

The Snapshot Date field did not have an impact in testing. Our charts with expression 1) were still blank... 😕

Alex

Re: Using input fields to select date range in set analysis

Gysbert_Wassenaar — Wed, 22 Jan 2014 18:10:15 GMT

A set analysis expression creates one set for the entire chart, not a set per row. It sounds like you want a set for each location. That would mean you can't use set analysis expressions. You'll have to use if statements I'm afraid.

topic Re: Using input fields to select date range in set analysis in QlikView

Using input fields to select date range in set analysis

Re: Using input fields to select date range in set analysis

Re: Using input fields to select date range in set analysis

Re: Using input fields to select date range in set analysis